Vlookup with Dynamic Range

[marcothy]

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

 

[rallcorn]

I have a vlookup that is having to go to 15 different "closed" workbooks to return a value.

INDIRECT requires that the source workbook be open, if it isn't open, INDIRECT returns the #REF! error value.

 

[marcothy]

Just now realizing the Indirect won't really populate the value.

I have tried this:
=VLOOKUP($A5,"'C:\Documents and Settings\use\Desktop\Current Month\\[Sales District("&$B5&") 201211.xls]Sales District'!$A$1:$F$65536",6,FALSE)

I get a #VALUE! error, but when I evaluate the error, it does show it as building out the file path correctly with the district # in the (). When I remove the &B5& and type the district # in, I get the same issue, but when I remove all the "", it works. Just cannot get it to maneuver off of that $B5 cell.

 

[T. Valko]

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

If you use INDIRECT then the source file MUST be open. This is usually undesirable especially if you're linking into many (15) files.

There is a free add-in that has a function called INDIRECT.EXT that works like the built-in INDIRECT function except that the source file does not need to be open.

However, this add-in has not been upgraded to work with the more recent versions of Excel.

If you're interested look here:

http://www.download.com/Morefunc/3000-2077_4-10423159.html

 

[marcothy]

I am guessing that add-in won't work in Excel 2007 then?

 

[T. Valko]

I am guessing that add-in won't work in Excel 2007 then?

I have the add-in loaded with Excel 2007 and there are a couple of minor user interface bugs but the functions themselves work just fine.