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.
Wyn 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.
Wyn Hopkins and mtarler you are right, power query is the way here. Our client is very sensitive for their proposal formats. It is not blocked, but rather not allowed by our regulations.
I will find out from the corporate if we can use power query.
I will mark your answers as Answer to my question.
Thanks,
A.
