Consolidating data from multiple worksheets automatically

[HunterSS]

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

[#2]

@HunterSS 

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

[#3]

@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.

[#4]

@Riny_van_Eekelen 

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.

[#5]

@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!

[#6]

@Riny_van_Eekelen 

Well, I've just taken delivery (although I've not opened the box yet) on the brand new Mac Mini, the one with the M1 chip, which I'll be connecting to two 24" screens. And I got it with pretty much the max of storage and speed....so maybe, once I get comfortable with it, I'll look into that. Up until this purchase I've been iMac and MacBook Air only.

But the truth is, as a retiree and mostly spreadsheet hobbyist--I use it for a lot of things personally, and very much enjoy adding features like the Dynamic Array functions--which have transformed my personal income/expense tracking spreadsheet--I mostly use it to track investing and small databases of personal interests. I like helping people here in the techcommunity, answering questions, explaining how to use Excel more effectively, but it's all as a hobbyist.

That said, intellectually, I'd like to reacquaint myself with the SQL-like aspects of Power Query, which I used to really enjoy  using with a mainframe IMB/DB2 database during my corporate days. But you don't seem think that the good folks at Microsoft have any plans to broaden out the availability of Power Query even though, let's face it, Apple's computers are really big portion of the market, even on the corporate scene. I read an article recently that talked of how at IBM, some IT guy (now that IBM no longer makes PCs) did a study, started giving employees the choice of Mac or WIndows, and found that the Mac users had fewer problems, cost less to maintain, fewer calls to IT for help, etc., etc.