Forum Discussion
Consolidation Table from Different Excel Tabs with Different Format
Hi all!
I have 5 (source) tabs with tables with data but in different formats. They all have common data but column headers are named differently. I want to have a separate tab (consolidation) that consolidates all but certain columns only: WBS code, WBS description, source type, location, source description, quantity, total price. I want the consolidation table to be updated instantly with every single update that happens to each of the 5 tables.
My preference is to have it formula driven as i want to have readily available in a tab without running macros. But if there is no other options, then VBA is the way.
Thank you in advance,
A.
- Almost certainly this is possible.
One solution may be to use Power Query to add the tables to the data model, link the appropriate columns and then create an output table.
Another option is to use Dynamic Array formulas to combine all the information
A third and possible best option is to create a master table with all the data and then have those other 5 tables actually pulled out from the master table based on some criteria (either with pivot table or DA formulas).
It is hard to know more of the details without a sample file.
8 Replies
- Almost certainly this is possible.
One solution may be to use Power Query to add the tables to the data model, link the appropriate columns and then create an output table.
Another option is to use Dynamic Array formulas to combine all the information
A third and possible best option is to create a master table with all the data and then have those other 5 tables actually pulled out from the master table based on some criteria (either with pivot table or DA formulas).
It is hard to know more of the details without a sample file.mtarlerThanks for the response. The tabs are stand alone tabs with different source of input and therefore cannot be used as one table.
Here is the link to the onedrive folder, filename is Sample.
In the sample I am using 4 source tables. Consolidation table (consolidation tab) must be instantly updated every time there is a change in any of the source tables.
https://1drv.ms/u/s!Aqv4zBSFNKaymTqUazTSHXO-2KcH?e=SPJv7z
Powerquery cannot be used as it is not allowed by the organization.
Thanks,
Alisher
- Interesting that you say Power Query is not allowed. I’m not aware of any way it can be blocked as it’s built in to Excel. Unless you have Excel 2010 or earlier ?
It is the simplest solution otherwise you need VBA
Hello! You've posted your question in the Tech Community Discussion space, which is intended for discussion around the Tech Community website itself, not product questions. I'm moving your question to the Microsoft Excel space - please post Excel questions here in the future.
- Thank you, Eric. Will do.
