Vlookup - Table array from a text string link

[Neilc78]

Hi all. Hope you can help me.

I want to use a vlookup function to search in a number of different files which are saved in folders by month & date. So what I am trying to do is have the following

Cell A1 - Month
Cell A2 - Date

I have Cell A3 = "C:\Finance\"&A1&"\["&A2&"]\Waste'!$B$8:$CO$112" (where waste is the tab in the file and B8:CO112 is the range in the file

so Cell A3 shows = C:\Finance\July\[07.01.2020.xlsm]\Waste'!$B$8:$CO$112

The next thing is I want to use Vlookup to search in that file for a value in Cell B1. So this is where it goes wrong for me.

I was thinking Vlookup(B1, A3, 2, False) but how do I get the vlookup function to look at A3 as a link to another cell as when I do it this way It doesnt work. Any help appreciated.

 

[Fluff]

Hi & welcome to MrExcel.
Try
Vlookup(B1, INDIRECT(A3), 2, False)

 

[RoryA]

The file would have to be open for INDIRECT to work...

 

[Neilc78]

Ah, that would be a problem then. Any other ways to do this?

 

[Fluff]

The file would have to be open for INDIRECT to work...

Excellent point.

 

[Fluff]

Any other ways to do this?

Not that I know of. But I could easily be wrong.

 

[RoryA]

You'd have to use code, or just use the 'edit links' dialog to change the source file when needed.