VLOOKUP and indirect references

**sportsaim** · 02-26-2008, 11:49 AM

I am trying to use VLOOKUP from another spreadsheet. Currently it looks like this: =VLOOKUP(C$1,'[Moneyline and Sec spreadsheet.xls]UTX'!$B:$F,5,FALSE) I would like the "UTX" to be a cell reference instead, ie cell A4. I would like to be able to change cell A4 from UTX to IBM and have the lookup look to a tab on my ohter spreadsheet which is the IBM tab. Is this possible?

Thanks

**NBVC** · 02-26-2008, 12:25 PM

TRY:

=VLOOKUP(C$1,INDIRECT("'[Moneyline and Sec spreadsheet.xls]"&A4&"'!$B:$F"),5,FALSE)

Note: This only works if all workbooks are open.

If the lookup book is closed, then you need to install the free Morefunc.xll addin from http://xcell05.free.fr/english/ and adjust formula to:

=VLOOKUP(C$1,INDIRECT.EXT("'[Moneyline and Sec spreadsheet.xls]"&A4&"'!$B:$F"),5,FALSE)

**sportsaim** · 02-26-2008, 03:13 PM

Thank you so much. I tried to add-in and it would not work. I am guessing because I have the other spreadsheet on a shared drive so it can't locate the spreadhseet.

**NBVC** · 02-26-2008, 04:20 PM

I think it should work...you just have to define the entire path in the formula.

**sportsaim** · 02-26-2008, 05:05 PM

would the parens, quotes, et. be in this order? I keep getting ?NAME Error.
=VLOOKUP(C$1,INDIRECT.ext("'S:\RSN\Trading\[Moneyline and Sec spreadsheet.xls]" & $A17 & "'!$b:$F"),5,FALSE)

**sportsaim** · 02-26-2008, 05:06 PM

Actually sorry, #NAME?. Tried to do it from memory since I am so used to seeing it on various other spreadsheets. Guess the memory is the first to go.