vlookup using cell as path

[orsm6]

hi all - been trying to use a vlookup formula to get a result. lots of variables in this.

- look up value is dynamic
- the path/file to be looked up is dynamic, so i was using a formula in this cell (K9) to build the path/file name

i tried to use this formula but all i get is #REF!: =VLOOKUP(C5,INDIRECT("'["&K9&"]Data'!$B$55:$C$80"),2,0)

the file is on a network path, hope that doesn't matter. When i directly code it in like this it works, even when the source book is closed.
VLOOKUP(C5,'\\mdzausutwfnp001\shardata\IL6S\07 Health, Safety & Environment\Pillar Work Processes\BOS\2021 BOS Tracking\2021 BOS Trackers\[2021 BOS Tracker - Hart Fill-Pack - DO NOT OVERWRITE.xlsm]Data'!$B$55:$C$80,2,0)

where am i going wrong?

TIA

 

[orsm6]

when you say highlight assuming you mean enter the formula as if you edit it?

see my reply above

 

[Alex Blakenburg]

If you need to open the other workbook.
Then in the 1st workbook, manually type in = then go to the other workbook and select that same data range.
Close the other workbook. You should now have the full address. Compare that to the f9 result character by character.

 

[orsm6]

If you need to open the other workbook.
Then in the 1st workbook, manually type in = then go to the other workbook and select that same data range.
Close the other workbook. You should now have the full address. Compare that to the f9 result character by character.

Oh... having the other workbook open actually makes it work... sorry that was the only thing i didn't try.

i did have it open at one point but the formula didn't work, so closed it without thinking about it... sorry
this should only work with the network workbook open yeah?

 

[Alex Blakenburg]

Oh... having the other workbook open actually makes it work... sorry that was the only thing i didn't try.

i did have it open at one point but the formula didn't work, so closed it without thinking about it... sorry
this should only work with the network workbook open yeah?

Sadly it does appear that Indirect won't work on a closed workbook.

Referencing value in a closed Excel workbook using INDIRECT?

I want to refer to a cell value in another closed workbook with a formula (not VBA!). The Sheet name is stored as a variable (in the following example, C13 is "Sheet2"). If the other file is open,...

stackoverflow.com

 

[orsm6]

Sadly it does appear that Indirect won't work on a closed workbook.

Referencing value in a closed Excel workbook using INDIRECT?

I want to refer to a cell value in another closed workbook with a formula (not VBA!). The Sheet name is stored as a variable (in the following example, C13 is "Sheet2"). If the other file is open,...

stackoverflow.com

Thanks heaps for your help though. great work.

 

[Alex Blakenburg]

Thanks heaps for your help though. great work.

Thank you. Happy to help.