Is there a way to automatically add rows to a worksheet to capture a SPILL range?

Doing spreadsheets work is like throwing a big party.

Where your analysis table is where you present the food to analyze whether it taste good by itself or what combinations of food is good together.

Its the same concept with numbers analysis.

So if you're going to throw a big party in your grand dining hall

(your analysis table), are you going to butcher a whole cow knowing that it will spill its guts all across your grand dining hall table.

or are you going to butcher that same cow in the kitchen?

after you butcher the cow you will pick which parts you're going to cook (apply operations, calculations arrangement equations) Much like a data model there are set hierarchal steps to process that cow (your big dataset) to prepare for the grand meal that you're going to present on your grand dining hall TABLE, so it can be tasted and analyzed.

Automation is "already knowing the process by heart" knowing which process can be automated by bulk and which ones ya have to manually arrange so when its all set up it will appear automated. when in reality you're using a blender (pivot table) to do this, a food processor to do that (power query) and a bunch of separate recipes to finally cook it up (formula functions).

Back to your question: Are you going to butcher that cow in your analysis spreadsheet knowing it will spill its guts all over the place.

If you've read my entire narrative I think you will have gotten my point by now.

cheers

Hi Joseph,

Thanks for using Microsoft products and posting in the community, I'm glad to offer help.

According to your description, I’m sorry I cannot understand your situation very well as you didn’t provide the exact formula and the referenced data set, it would be helpful to us to reproduce the problem if you could provide them, your understanding and cooperation will be highly apparated.

Besides, you can refer to the following article to get some information on how to resolve the #SPILL! Error: #SPILL! error - Extends beyond the worksheet's edge (microsoft.com)

Looking forward to your feedback.

Best Regards,

Arck

Thank you for the reply, I understand what you're getting at.

My issue is that while the amount of blood and guts will vary from cow to cow, I always need the specific cuts of meat in the same specific location.

In my example above, all of the A1010 coded items in the dataset need to go under the A1010 section, but depending on the dataset, that could be (10) items or (100) items.

The reason I am trying to do it this way is that I have (3) dining room tables that slice and dice the data different ways depending on the code, and ideally, I'd like to not have to come into each section and add rows until the SPILL range is captured. I'd like for the software to always know how many guests are coming to dinner, and set (expand) the tables accordingly.

Best,

Hi Arck -

Thanks for the reply. The formula in B10 in the first screenshot above is as follows.

=FILTER('DATASET'!$B:$F,'DATASET'!$L:$L=$B$9,"")

This formula is used throughout the workbook to search the dataset and find everything with a code = to cell B9 and return that entry under that section.

Maybe you'll need a food processor (power query) so you can select your meats in separate little tables and have the food processor combine the little tables into one big table bey either doing an append query or a merge query depending on how your dining guests are showing up the arrangement can be different append is just to elongate the table while merge is to widen the table with redundant columns that you can optionally remove and the power query does the same exact steps as you initially did when you created it on the same formatted dataset over and over by just pressing ctrl+alt+F5, and if you prefer to pre-cut your meat into little csv files you can do a query by folder where you drop your csv files (all having the same format and having the same headers) and power query will just automatically combine all of them into 1 big dining table

You can also use a Dat-a Model and create a table to do your analysis by using measures to combine the data using a pivot table (power pivot)

Here's an article you might be interested:

Power Query / Get & Transform - Pass value as query parameter - Microsoft Community

Here's a neat formula so you'll know ahead of time how many items your spill might be and even use it to get the relative position

Excel formula: Get relative row numbers in range | Exceljet

This formula of yours

=FILTER('DATASET'!$B:$F,'DATASET'!$L:$L=$B$9,"")

can also get a count of how many by using the formula:

=COUNTA(FILTER($B:$F,ISNUMBER(FIND($B$9,$B:$F,1)),""))

Those numbers:

555

556

557

are those phase numbers or sub number the the main phase number?

from the looks of your sample screen shots a data model would likely be the best option for you. Setting up the data model using power query then presenting your power query data results into a power pivot (which dynamically updates (add new rows) relative to the dynamic update of the power query dataset I believe would be a great dynamic solution for your issue.

if you can share a dummy file reflecting a real scenario, we can create a data model based on those phase numbers or sub-phase number to dynamically update the final presentation pivot table as a solution.

Yea So - thanks so much for all of your helpful suggestions. I'm going to post a link below to a dummy file - giving an example of the dataset I'm working with, and will try my best to simply explain better what I'm looking to do through automation. I think its important to know this is a presentation report tool, not a database/data store

• Automatically sort the data by Column Z (Column 24) while summing all of Column G (Total) in a section heading, a bit like this

  • Level 1 - A10 Foundations - Total All Items with 'A10 Foundations' code in Column I

    • Level 2 - A1020 - Total All Items with 'A1020' code in Column Z (I want to use column Z to keep them in the order they appear in the data set

      • Level 3 - Here I want to see all of the detailed line items (Columns C through G) in the order they occur in Column Z - I don't care about presenting anything past the Column G totals

• Same as above, but automatically sort the data by Column AA (Column 25) while summing all of Column G (Total) in a section heading, a bit like this - this one is simplified as it has one less level

  • Level 1 - Concrete - Cast In Place - Total All Items with 'Concrete - Cast In Place' code in Column J

    • Level 2 - Here I want to see all of the detailed line items (Columns C through G) in the order they occur in Column AA - I don't care about presenting anything past the Column G totals

• Automatically sort the data by Column AB (Column 26) while summing all of Column G (Total) in a section heading, a bit like this

  • Level 1 - 3 - Total All Items with '3' code in Column M

    • Level 2 - 03 30 - Total All Items with '03 30' code in Column AB (I want to use column AB to keep them in the order they appear in the data set

      • Level 3 - Here I want to see all of the detailed line items (Columns C through G) in the order they occur in Column AB - I don't care about presenting anything past the Column G totals

I hope this all makes sense, and that the macro/VBA wizards can point me in the right direction - in essence I've been cutting/pasting this data around as my method of sorting - need a better solution that still gives me the heading totals I need

TIA