I've attached an example of my workbook.
Essentially on my master sheet tab I'm trying to get a total for each product code for each date from every worksheet in the workbook.
- The cell references are not in the same location on each sheet.
- Some sheets may not have all the products listed on the master or for that day the product may have no quantity
- The quantity of worksheets will grow in the future.
- The quantity of products may grow in the future.
- I want to make it as dynamic as possible so I can pass it off to someone else and not have to fix it every time a new item is added (named ranges will obviously help with this)
Any help would be appreciated.
Hi Calla
One method is by using the Data-Consolidate option.
1) Add a new worksheet and select cell A1.
2) From the new worksheet select Data-Consolidate
3) From the Use labels in, check the Top row and Left column options.
4) In the Reference select the range to consolidate from each worksheet, include row and column header (1 row and 1 column only) and click Add
For example from the AlsCafe worksheet, select the range C5:R13
5) Repeat step (4) for the other ranges.
6) Once you have finished selecting, click OK and the data will be consolidated. You many need to format the date.
I have included a screen shot of the selections in the attached file.
Hope this helps.
Sunny
Thank you SunnyKow, I appreciate the feedback.
However this doesn't meet my needs because every time I would want the data consolidated I would need to run through this procedure, which is time consuming and as I specified in the workbook I have way more than 4 tabs that needs consolidation. Also this data consolidation is limited because it does not automatically account for new products being added to the existing ranges.
Any other ideas?
In case any one else is dealing with a similar issue I thought I would provide the solution I settled on. So I took all my worksheets and used Power Query. My worksheets are identical copies of each other apart from the list of products being shorter and in a different order from one another so I was able to find a set of query instructions that including deleting columns and rows, using the first row as headers and then highlighting the majority of columns and unpivoting columns. Then I went to view --> advanced editor and copied the instructions so that I could paste them for each of the worksheets. Finally I merged the queries together to get my own little (big) table that had all the data I needed to use in a pivot table.
Hi Calla,
Glad you settled on Power Query. That would have been my recommendation if Consolidate didn't work.
Mynda
Hi All,
just a small typo in LittleDog!C14: Croissant-Swiss needs an extra "i".
I agree to settle on Power Query.
An old style formula solution would request to list sheets names (in the example in Totals!A6:A9) in order to exploit a sumproduct
In D6 to be copied across
=SUMPRODUCT(SUMIF(INDIRECT("'"&$A$6:$A$9&"'!C6:C20"),$C6,INDIRECT("'"&$A$6:$A$9&"'!"&ADDRESS(6,COLUMN(D$1))&":"&ADDRESS(20,COLUMN(D$1)))))
Again, Power Query is the future.
