Forum Discussion
Consolidation Table from Different Excel Tabs with Different Format
- 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.
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 VBAWyn Hopkins that is a good point, I don't know how they would block it. but it is possible using formulas. this isn't the prettiest or most efficient but seems to work.
Hah! That's some crazy fancy formula writing mtarler 😁, nice use of LET and Dynamic Array functionality. Also with the benefit it will work in Excel online.
Hopefully alex_n and any other user of the file has the Dynamic Array version of Excel with LET
Personally I'd recommend the Power Query Approach if at all possible from a simplicity and robustness viewpoint. You could then add a a simple Macro that triggers a refresh when consolidation page is selected.
