Summary Sheet with multiple different tabs with different information

Copper Contributor

I have a summary sheet I need to make. It goes through multiple sheets with different data. 


This is the blank Summary now, the pastel highlighted cells are the ones I need filled with data in multiple sheets.



Here is the first sheet. Tab name format is "date flavour". The sheet only has 1 flavour but in multiple batches. I need the order quantity of D2, D7, & so on added to total on the summary sheet. Same with E & M in their corresponding cell on the summary sheet. Now that I'm thinking of it, I may have to change M because some ingredients do over lap between different flavours (chocolate orange, Chocolate mint both have chocolate so I might need a separate summary sheet for ingredients... 


sheet 1.png


Sheet 3 with a different flavour that has to be summed up in its corresponding cell in the summary sheet.

Sheet 3.png


Any Idea how to do this? sheets will be added as needed.

4 Replies

@Enrewen Assuming you can rely on the exact same row and column order in the 'date Flavour' sheets, your simplest solution would be this:

  1. Insert a worksheet immediately before the first flavour sheet. Call it something simple like "Start"
  2. In cell A1 of Start, enter something to make clear that sheet is supposed to stay empty
  3. In cell A2, add some text to explain new Flavour sheets MUST go between the Start and End sheets
  4. Repeat 1-3, this time call the sheet "End" and move it so it comes after the last flavour sheet
  5. To sum all D2 cells of the Flavour sheets, you simply enter:




They don't stay in the same spot. Each tab is a different flavour & some flavours have more ingredients so they may take extra rows. I need all Flavour 1 in the flavour 1 summary, flavour 2 in the flavour 2 summary, etc. but we might do 3-4 flavours a month (1 per day) & each day is a different tab. there could be up to 15-20 tabs a month.
This is a great example of how splitting things over different tabs causes issues when you want to report over your data. I would want to point you at the SUMIFS function, but that won't work across multiple tabs.
I think your best bet is to use Data, Get Data, From File, From Workbook and go from there, but that is not so simple to explain.
How about merge all tables to one master table and then sum up?