Forum Discussion
Consolidating data from multiple worksheets automatically
Hi,
In 1 workbook i presently have 4 worksheets:
Sheet 1 - Overall Register
Sheet 2 - Document Register
Sheet 3 - Form Register
Sheet 4 - Presentation Register
Each sheet has the same 10 column headings
The idea is that we register a document/ form/ presentation into the relevant sheet (which also gives it a specific file name), I am trying to then gather all of this information into the overall register worksheet - with it automatically updating as new items are registered.
I have tried the Data Consolidate function but it is not pulling forward all the information, nor is it placing it down the sheet - more across - which is definitely not what i am wanting.
Any help would be gratefully received
Thank you
5 Replies
HunterSS I agree with mathetes that, in principle, it would be better to collect all data in one table and extract whatever summary you may need from that. But, if you are somehow forced to collect the data in three different tables, you can use Get&Transform Data (a.k.a. Power Query, PQ) and combine the three tables into one Overall table. The attached workbook contains a simplified example. The "Overall" sheet also contains a link to a website (one of many) where you can read more about combining multiple sheets into one.
PQ is, in principle, Windows only! Some limited functionality exists in Excel for the Mac (like refresh for certain types of queries), though you can not create new queries or edit existing ones.
PQ is, in principle, Windows only! Some limited functionality exists in Excel for the Mac (like refresh for certain types of queries), though you can not create new queries or edit existing ones.
Frustrating to me, a confirmed and committed Mac user. But I get by without it. There's still so much magic in Excel, I never tire of learning new things.
mathetes Thought the same until about a year ago 🙂
The good thing is that you can have the best of both worlds, running W10 / Excel on a virtual machine on your Mac. As long as your Mac isn't too old or short on memory and processing power. I'm running it on a 2012 (!) MB Pro, hooked up to a 32" screen and it's pretty good, I would say!
Sounds like a classic case of we humans getting in the way of letting Excel do what it does very well, which is to take a single database and produce extracts, summary analyses, etc. We like to create separate lists and then ask Excel to put them together, which is possible, but (as you're experiencing) less than easy.
Why not use your overall register as the main data base, the Input side of things, and then let Excel do it's thing of extracting only the Documents or Forms or Presentations, on demand (the Output end of things). Excel actually excels at that quite easily. Especially now, with newly available Dynamic Array functions like FILTER and SORT.
Here's a YouTube video that was used to introduce these Dynamic Array functions. It might be just what you're seeking. https://www.youtube.com/watch?v=9I9DtFOVPIg
