Match Function Problem

[cookeetree]

G’day Excel Gods,

I have multiple files that reference a single MASTERFILEfor specific data. Traditionally, I have used a standard VLOOKUP formula, but this runs into problems if you want to add a column in the middle of the reference sheet later. It throws out all of the reference column numbers after this new column.

In what I thought was a moment of brilliance, I realised I could use MATCH to return the column number and inserted this into my VLOOKUP formulas. I believed that the cell reference in the MATCH function would automatically update if I added a column to the MASTERFILE.

I was wrong.

Unless the files that reference the MASTERFILE are open while adding the new column, they don't update. I’ve tried making them absolute references, I’ve tried making them relative and I’ve tried making them mixed. Nothing works unless the file referencing the MASTERFILE is open at the same time.


Is there a better way of doing this? Is there a way making what I’m currently doing work without having to open EVERY file that references the MASTERFILE???

Thanks in advance,

Jason.

 

[Artik]

You can't do anything with formulas. This is a flaw in external appeals.
The only solution that comes to my mind is to run a macro before inserting the column, which will open all files that should update.


Artik

 

[cookeetree]

You can't do anything with formulas. This is a flaw in external appeals.
The only solution that comes to my mind is to run a macro before inserting the column, which will open all files that should update.


Artik

Oh, no!

Thanks for your reply.

 

[sandy666]

IMHO PowerQuery (Get&Trannsform) would help

 

[cookeetree]

IMHO PowerQuery (Get&Trannsform) would help

That's uncharted territory for me. I'll look into it.

Thanks for the reply.

 

[sandy666]

sure

What is PowerQuery?

 

[Fazza]

An alternative to (VLOOKUP) formulas are queries. These work with header names (or could be 'select all', which is independent of header names). So inserting extra columns is not an issue. Changing header names might be.

If using queries, watch out for changing the file path for the master file. With VBA there is more control though they can be done without VBA. For example, set the dependent workbooks to have their queries update automatically on file open. This technology has been around for 20+ years but if you're not familiar with it will need a little learning. And if you're too far down the track with the current setup it might be too late to change.

A slight variation on the idea is to have the master data in an mdb file. [Or, I guess txt, or csv.]