Vlookup - dynamic change to file path

I have four files stored in this folder: C:\Users\JWilli80
North.xlsx East.xlsx West.xlsx South.xlsx

On the attached file there is lookup formula in cells B7 to B10.
At the moment it is currently hard coded for a vlookup to the North file.
In cell B1 there is a regional choice of the four regions.

What I want to happen is that when someone changes the region choice in cell B1, the region name in the lookup path formula in cells B7 to B10 automatically change.
=VLOOKUP(A7,'C:\Users\JWilli80\[North.xlsx]Sheet1'!$A:$B,2,FALSE)
=VLOOKUP(A7,'C:\Users\JWilli80\[East.xlsx]Sheet1'!$A:$B,2,FALSE)
=VLOOKUP(A7,'C:\Users\JWilli80\[West.xlsx]Sheet1'!$A:$B,2,FALSE)
=VLOOKUP(A7,'C:\Users\JWilli80\[South.xlsx]Sheet1'!$A:$B,2,FALSE)
I’m guessing there will need to be a break in the path where the & sign is used and a reference to cell B1 is placed somewhere in the middle?

I'm not sure, but this seems like a perfect example of when to use Power Query. I don't think it's available in V2007 though. Let us know if you've upgraded.

Thanks for pointing that out, I have updated my profile as I'm using MS365.

Originally Posted by 6StringJazzer

... There are solutions that don't require the file to be open, but they all require VBA in some form.

Not all

=VLOOKUP(A7,
CHOOSE(MATCH($B$1,{"North","East","West","South"},0),
'C:\Users\JWilli80\[North.xlsx]Sheet1'!$A:$B,
'C:\Users\JWilli80\[East.xlsx]Sheet1'!$A:$B,
'C:\Users\JWilli80\[West.xlsx]Sheet1'!$A:$B,
'C:\Users\JWilli80\[South.xlsx]Sheet1'!$A:$B),
2,FALSE)

Using Power Query would work well with this I think. The files would not need to be open. The attached loads the data from the 4 files in another folder (if you add more files (like SouthEast, NorthWest, etc., or add more data to any of those files it will automatically bring that data in as well).

I'm not well-versed in PQ - just have been using it for the basics, but I can see it's great to use

See attached.

Originally Posted by Gregb11

Using Power Query would work well with this I think. The files would not need to be open. The attached loads the data from the 4 files in another folder (if you add more files (like SouthEast, NorthWest or , etc., or add more data to any of those files it will automatically bring that data in as well).

I'm not well-versed in PQ - just have been using it for the basics, but I can see it's great to use

See attached.

I wonder how it works when adding new files to the folder - does Power Query find and update the data itself? Or do you have to manually update something?

I wonder how it works when adding new files to the folder - does Power Query find and update the data itself? Or do you have to manually update something?

As stated,

if you add more files (like SouthEast, NorthWest, etc., or add more data to any of those files it will automatically bring that data in as well

So no, you don't have to manually update something.

(to update your main file with PQ, you do need click the Refresh option, but that's it).