May 5, 2016
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.
June 25, 2016
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.
May 5, 2016
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?
May 5, 2016
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.
July 16, 2010
July 3, 2016
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
Again, Power Query is the future.
Most Users Ever Online: 57
Currently Online: Mynda Treacy
Currently Browsing this Page:
Frans Visser: 210
mey tithveasna: 71
Anders Sehlstedt: 47
Guest Posters: 1
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas