I have a vlookup that is having to go to 15 different "closed" workbooks to return a value.
The 15 books are all named the same with a specific portion that is variable. That variable is stored in a cell on the sheet I am using.
Right now, the formula I am struggling to write is this:
=VLOOKUP($A5,'C:\Documents and Settings\user\Desktop\Current Month\[ Sales District(123456) 201211.xls]Sales District'!$A$1:$F$5000,6,false)
The district number (what is in bold), that is part of the filename and it may change depending on the row record it is in. That is populated from column B.
What I have tried and failed so far is in using the indirect function to set that variable. I have tried with portions of the string using "" and not using "" to no end.
My failure, so you can see what I am attempting looks like this:
=VLOOKUP($A5,'C:\Documents and Settings\user\Desktop\Current Month\[ Sales District(&Indirect($B5)&) 201211.xls]Sales District'!$A$1:$F$5000,6,false)
Any help on making this dynamic is greatly appreciated.
Thanks
The 15 books are all named the same with a specific portion that is variable. That variable is stored in a cell on the sheet I am using.
Right now, the formula I am struggling to write is this:
=VLOOKUP($A5,'C:\Documents and Settings\user\Desktop\Current Month\[ Sales District(123456) 201211.xls]Sales District'!$A$1:$F$5000,6,false)
The district number (what is in bold), that is part of the filename and it may change depending on the row record it is in. That is populated from column B.
What I have tried and failed so far is in using the indirect function to set that variable. I have tried with portions of the string using "" and not using "" to no end.
My failure, so you can see what I am attempting looks like this:
=VLOOKUP($A5,'C:\Documents and Settings\user\Desktop\Current Month\[ Sales District(&Indirect($B5)&) 201211.xls]Sales District'!$A$1:$F$5000,6,false)
Any help on making this dynamic is greatly appreciated.
Thanks