How to update unlinked columns according to content of a column linked to a external source file?

Copper Contributor

Hello to all of you.

Situation: I have created an excel file with about 20 columns, of which the first 4 columns (A to D) are linked (external references) from a source file. When I update the source file, columns A to D in the target file automatically update as desired, but all the rows of the other columns from column E onwards do not follow, which makes sense. Currently, I have to manually move the data by copy and paste (values) from column E down from the new row created in the sourche file to get each row to line up with the correct data. Having conditional formatting rules all over the place, I have to be careful not to do normal copy and paste. I can't use VBA (and anyway, I have no idea how to do it, my Excel knowledge being basic), my company doesn't allow me to.

Question: Does anyone know how to make the columns from E onwards always stay up to date with the content of the columns from A to D? I tried "formatting as a table", thinking that the rest would "understand" what happens when the source file is updated, but it did not work. Maybe I should use a formula instead of a link to get the source data from another file?

 

Your help is greatly appreciated and I thank you for your support.

7 Replies

@Poubel 

Do the columns from E on have an explicit relationship with columns A to D - do they have a value in common, for example?

Hi @Hans Vogelaar thanks for your question.

 

No, the columns after E do not get/fetch any infos from the columns A to D. I just need the ranges to be correctly aligned to oneanother, whenever A - D are updated from another excel file.

@Poubel 

I don't see how that would be possible if there is no link between the two sets of data.

Column A in the source file is empty. If I added a unique number for each range from 1 to n, would that be then possible?

@Poubel 

You'd have to create a separate lookup range, with the numbers 1 to n in a column, and the corresponding data intended for columns E, F, ... next to that column.

You can then use VLOOKUP or XLOOKUP to place the correct data next to columns A to D.

@Poubel 

Power Query could work if you have unique ID. Not straightforward solution and requires refresh, nonetheless.

You may query data in Sheet1, return result in Sheet2, add any columns you wish, query it again and merge with the source. If that's an option you may google for self-referencing tables.

Thanks, I'll give it a try and will come back to you sometime next week.