Consolidation of tables using append needs to cope with changing column headings and column order

[Helzp]

Hi all,
I have an excel with five sheets of tables, with some columns in common.
I would like to consolidate the tables into a master sheet, which I have successfully done using “append”.

However the team are changing the order of the columns, adding new columns, deleting some, changing column names in the base data

Is there a way I can make my queries and append dynamic so i do not get an error and have to start from scratch every time they request a new base table layout?

Many thanks! Keen to learn!
kind regards
Helzp

 

[sandy666]

could you be more clear?
or post link to the shared excel file with representative examples of source data (before changes and after) and the result you want to achieve
use onedrive, googledrive , dropbox or any similar service to share your file

 

[Helzp]

Hi Sandy,
Thanks for your response. Please let me know if this link works with you
Append Query Helz.xlsx Does this work?

I am trying to consolidate tables into a common table. I have three scenarios, where my boss requests one or multiple of the following changes to the source tables.
1) Change the source column header names
2) Remove a source column
3) Add a source column that in the future can be shown in my consolidated table
THe result is I make the changes and the consolidated table now does not refresh (which makes sense).
I have no written code, I have just selected get data -> append to create my consolidated table.

Many thanks, let me know if I can give extra detail! I appreciate your time

 

[sandy666]

Yeah, I got this file

edit:
change boss because he doesn't know what he really want [joke]

 

[Helzp]

Who says the boss is a he
It’s also a stretch goal for myself so taking it as a learning opportunity! But I have reached the limit of my abilities...

 

[sandy666]

she should look in the mirror and do eg. make-up or something

there is no more columns than that?
and order of headers is always the same?

Title	Start Date	Finish Date	Owner AU	Owner US

 

[Helzp]

Order of headings always the same but if one is deleted, the rest will shift left etc.
there may be additional columns added in the future

looking for a generic answer to any of those three questions if it exists!

 

[sandy666]

looking for a generic answer to any of those three questions if it exists!

yeah, I know
It will be a bit complicated
I think I'll try it in the morning (coffee doesn't work now)

 

[Helzp]

No rush! Just if you find some spare time thanks!!

 

[sandy666]

here is, just test it

s666-PQ-Append Query Helz.xlsx

Microsoft Excel Workbook

1drv.ms