External reference lookups based on contents of other cells

[Dennis Carr]

OK, here's a weird situation. I want to use @lookup (sorry, I'm an
old Lotus hat) to do the usual referencing, however the file(s) I want
to use as my lookup table will be based on the contents of a nearby
cell in the originating spreadsheet.

Previous attempts with
=lookup(cell,'d:\path\to\file\(cell)'range1:range2) have come up null
- in theory, I think this should work.

Anyone have any suggestions?

-Dennis Carr

 

[Harlan Grove]

OK, here's a weird situation. I want to use @lookup (sorry, I'm an
old Lotus hat) . . .

Perhaps too old. 123 has @VLOOKUP and @HLOOKUP, never has had @LOOKUP. In any
case, get used to eliminating the @s.

. . . to do the usual referencing, however the file(s) I want
to use as my lookup table will be based on the contents of a nearby
cell in the originating spreadsheet.

Previous attempts with
=lookup(cell,'d:\path\to\file\(cell)'range1:range2) have come up null
- in theory, I think this should work.

Do you mean that (cell) is a cell reference that you want to interpret as a
worksheet name within the workbook d:\path\to\file.xls, so that the entire
reference given as a text formula would be something like

"d:\path\to\[file.xls]"&cell&"'!X99:Y666"

? If so, then would this file, d:\path\to\file.xls, be open in Excel when you're
trying to access its data? If so, try

=LOOKUP(A5,INDIRECT("'[file.xls]"&cell&"'!X99:Y666"))

Otherwise, if this file may be closed, you're going to need some trickery. See
the linked article for alternatives.

http://www.google.com/[email protected]