Sep 24 2021 08:09 AM
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.
Sep 24 2021 10:29 AM
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.
Sep 24 2021 11:27 AM
SolutionSep 24 2021 11:39 AM
@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
Sep 24 2021 11:40 AM
Sep 24 2021 04:29 PM
Sep 24 2021 05:23 PM
@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.
Sep 24 2021 09:18 PM
Hah! That's some crazy fancy formula writing @mtarler :beaming_face_with_smiling_eyes:, 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.
Sep 27 2021 06:35 AM
@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.
Sep 24 2021 11:27 AM
Solution