vlookup using cell as path

orsm6

Active Member
Joined
Oct 3, 2012
Messages
399
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,657
Office Version
  1. 365
Platform
  1. Windows
I believe the issue is that you have the "[" which needs to precede just the filename part of the path/file name in front of the full path/file name. (just the file name is enclosed in "[ ]").

How are you sourcing the file path/name ?
If its convenient split path & file name into 2 separate cells.
Or is it convenient to have the square brackets already in K9 ?

If not and you want to do it all in the one formula, you are going wind up with quite a long formula since the formula to split the path/filename is quite long.

To get just the name part:
per Extracting File Names from a Path (Microsoft Excel)
=MID(K9,FIND(CHAR(1),SUBSTITUTE(K9,"\",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(K9,"\",""))))+1,LEN(K9))
A shorter formula can be used if you are sure that the filename will never be more than 99 characters long:
=TRIM(RIGHT(SUBSTITUTE(A2,"\",REPT(" ",100)),99))

Your post with the "[" position marked up:

1616657738517.png
 

orsm6

Active Member
Joined
Oct 3, 2012
Messages
399
Office Version
  1. 365
Platform
  1. Windows
I believe the issue is that you have the "[" which needs to precede just the filename part of the path/file name in front of the full path/file name. (just the file name is enclosed in "[ ]").

How are you sourcing the file path/name ?
If its convenient split path & file name into 2 separate cells.
Or is it convenient to have the square brackets already in K9 ?

If not and you want to do it all in the one formula, you are going wind up with quite a long formula since the formula to split the path/filename is quite long.

To get just the name part:
per Extracting File Names from a Path (Microsoft Excel)
=MID(K9,FIND(CHAR(1),SUBSTITUTE(K9,"\",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(K9,"\",""))))+1,LEN(K9))
A shorter formula can be used if you are sure that the filename will never be more than 99 characters long:
=TRIM(RIGHT(SUBSTITUTE(A2,"\",REPT(" ",100)),99))

Your post with the "[" position marked up:

View attachment 35289
Hi Alex - thanks for your help. Yes i can split it into two cells if you like. I originally did it that way.

to get the path and file name source here are the formulas I used
path K9 ="\\mdzausutwfnp001\shardata\IL6S\07 Health, Safety & Environment\Pillar Work Processes\BOS\"&C3&" BOS Tracking\"&C3&" BOS Trackers\"
file L9 =C3&" BOS Tracker - "&C4&" - DO NOT OVERWRITE.xlsm"

as for 99 characters long or not.... unsure.\

cheers.
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,657
Office Version
  1. 365
Platform
  1. Windows
Hi Alex - thanks for your help. Yes i can split it into two cells if you like. I originally did it that way.

to get the path and file name source here are the formulas I used
path K9 ="\\mdzausutwfnp001\shardata\IL6S\07 Health, Safety & Environment\Pillar Work Processes\BOS\"&C3&" BOS Tracking\"&C3&" BOS Trackers\"
file L9 =C3&" BOS Tracker - "&C4&" - DO NOT OVERWRITE.xlsm"

as for 99 characters long or not.... unsure.\

cheers.

So if you change your formula to the below does it work ?

Excel Formula:
=VLOOKUP(C5,INDIRECT("'"&K9&"["&L9&"]Data'!$B$55:$C$80"),2,0)
 
Solution

orsm6

Active Member
Joined
Oct 3, 2012
Messages
399
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I
So if you change your formula to the below does it work ?

Excel Formula:
=VLOOKUP(C5,INDIRECT("'"&K9&"["&L9&"]Data'!$B$55:$C$80"),2,0)
still get #REF!
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,657
Office Version
  1. 365
Platform
  1. Windows
Highlight the index formula and hit f9. Copy the result into a cell. Make sure to escape out or you will overwrite your formula
 

orsm6

Active Member
Joined
Oct 3, 2012
Messages
399
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Highlight the index formula and hit f9. Copy the result into a cell. Make sure to escape out or you will overwrite your formula
it still does REF

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

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,657
Office Version
  1. 365
Platform
  1. Windows
Oops sorry you need whats inside the indirect formula not the actual formula
 

orsm6

Active Member
Joined
Oct 3, 2012
Messages
399
Office Version
  1. 365
Platform
  1. Windows
Oops sorry you need whats inside the indirect formula not the actual formula
yeah ok got ya.... this is the result

"'\\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"

this is the network path copied via 'copy path'
"\\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"
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,657
Office Version
  1. 365
Platform
  1. Windows
when you say highlight assuming you mean enter the formula as if you edit it?
[/QUOTE]

Go into edit mode on the formula. In the edit bar highlight what's inside the index function.
Hit f9, it should convert to the full file name path etc.
Copy that into a cell and compare it to what it should be.
 

Forum statistics

Threads
1,141,060
Messages
5,704,041
Members
421,324
Latest member
Devo182

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top