Forum Discussion
Enrewen
Mar 21, 2024Copper Contributor
Summary Sheet with multiple different tabs with different information
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 mu...
JKPieterse
Mar 21, 2024Silver Contributor
Enrewen Assuming you can rely on the exact same row and column order in the 'date Flavour' sheets, your simplest solution would be this:
- Insert a worksheet immediately before the first flavour sheet. Call it something simple like "Start"
- In cell A1 of Start, enter something to make clear that sheet is supposed to stay empty
- In cell A2, add some text to explain new Flavour sheets MUST go between the Start and End sheets
- Repeat 1-3, this time call the sheet "End" and move it so it comes after the last flavour sheet
- To sum all D2 cells of the Flavour sheets, you simply enter:
=SUM(Start:End!D2)
- EnrewenMar 21, 2024Copper ContributorThey 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.
- peiyezhuMar 21, 2024Bronze ContributorHow about merge all tables to one master table and then sum up?
- JKPieterseMar 21, 2024Silver ContributorThis 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.