Forum Discussion
How to combine the rows in multiple worksheets into one master sheet?
Apologies if the solution is trivially obvious but for the life of me I can't find the solution. Here is the problem:
I have two worksheets functioning as a document log of sorts. Each worksheet represents a particular category of documents. They both have identical fields. I would like to have a third worksheet that serves as a master. This third worksheet would be populated by any additions made to the other two worksheets. It would also automatically propagate any edits/changes made to the corresponding row in the other worksheets.
Is there a way to automate master worksheet population without using macros? As it is right now, I have had to manually copy and paste rows into the master worksheet and ensure that any edits were also copied over. I prefer to have each of the two worksheets serve as the single source of truth for the data in the master worksheet.
System Specifications:
Microsoft Excel for Mac
Version 16.63.1 (22071301)
MacOS Ventura 13.0 (Beta 5)
PS
In various support forums and videos, I see others have multiple options when using Power Query. On my system I see only two: Excel Workbook or Text/CSV. None of the myriads of options I see on support forums. Is this a limitation that exists only on Mac versions?
LegalSculptor First of all, Excel for Mac doesn't yet support all PQ functionality you may have read about. That said, why would you maintain two separate sheets, wanting to create a third as a master? Put all in one sheet, add a column for the category and work with that.
- This is not helpful. I'm in a similar situation as OP. We have a project document with separate tabs for each project manager, but we want to see a consolidated list of all active projects. I'm trying to pull the data from each tab into a single worksheet but I can't find a simple solution. The column headings are all the same.
Using a single worksheet isn't practical since each project manager edits/manages their projects individually. But we do need an overarching status of each project.PoKaroo Are you also using a Mac?
- Unrestricted fully up to date Office 365 for Mac subscribers now have full PowerQuery capabilities.
JimGMac Which "unrestricted" version would that be. I'm on 16.64 (22073100). It certainly has a great deal of PQ but I wouldn't call it "full capabilities". Am I missing out on something?
Riny_van_Eekelen That certainly makes sense. But my employer prefers that it is presented in this manner (simply pivoting on one worksheet is not desirable.) 🤷🏽:male_sign:
LegalSculptor Perhaps convince your employer that he/she is wrong.
