Register
Log In
Unanswered
Home
Dashboards
May 5, 2016
Offline
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.
Dashboards
June 25, 2016
Offline
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
Dashboards
May 5, 2016
Offline
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?
Dashboards
May 5, 2016
Offline
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
Online
Hi Calla,
Glad you settled on Power Query. That would have been my recommendation if Consolidate didn't work.
Mynda
July 3, 2016
Offline
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.
Most Users Ever Online: 57
Currently Online: Mynda Treacy
13 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
Md. Rahman
Lilian Vo
Jane Kumwenda
David Miller
Efe Onwochei
LynAnn Williams
Brandy Kriger
purna chandar
E K
velissa barr
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
