Return a value in a range on another spreadsheet using VLOOKUP?

[drawkcaB]

Hello,

I have two spreadsheets. In one spreasheet I have a sheet I wish to populate (sheet A), while the other spreadsheet (sheet B) has the array I want to use. Normally I'd simply use "insert function" and when prompted to I'd select by table range, but I'd like to call the array using an existing text string in another cell on sheet A.

The text string in this instance is the location of the range I want to use, i.e.

N:\[...]\TEST\[SheetB.xls]Header!headerArray

located in Cell W56 (also the string in W56 is a concatenated from several other cells)

But when I do

=VLOOKUP("25NN2",W56,3,FALSE)

I get a #N/A error.

Please help

[Cheeky Charlie]

You can use indirect() to turn a string into a reference (e.g. =VLOOKUP("25NN2",indirect(W56),3,FALSE)), but if you use it a lot Excel will grind to a halt (even with a really good computer) as it is a very memory-intensive function.

More often than not you can use more efficient functions in place of indirect.

hth

[DonkeyOte]

Further to CC's point - if the link is external and closed (implied by the path) then the native INDIRECT will not work I'm afraid (requires open target).

If that is indeed an issue I would suggest googling/searching the board for the likes of INDIRECT.EXT, and Harlan Grove PULL function

[drawkcaB]

Sheet A currently references other worksheets in Sheet B using the indirect function. I tried using INDIRECT.EXT a few months back and found it unreliable. It's not overly important to my application fortunately so INDIRECT can be used instead.

For specifics, I have 300 instruments data sheets to fill out. Because of the nature of our project, there's actually only 8 types of identical instruments, but each is repeated many, many times with different tagging, etc. The idea is that I'll have 300 "Sheet A" data sheets that autopopulate with the information contained in the worksheets in "Sheet B". The user is intended to only work on the spreadsheet with the information, not work with the data sheets containing all the INDIRECT functions.

Adding INDIRECT(W56) returned a #ref error.

What the are the other methods I can try to get this to work?

[Cheeky Charlie]

Adding INDIRECT(W56) returned a #ref error.

Implies you've got the path wrong - have you checked it?

[drawkcaB]

I checked the path and it still doesn't work. It's almost the same path used in other cells to populate the data sheet except those cells don't use VLOOKUP. When I use the error checker, the table_array value inserted via INDIRECT(W56) is correct, but it still returns #ref.

Is there any other methods I can use to get this working?

[Cheeky Charlie]

Given the other workbook has to be open, perhaps it would work if to return the short version of the filename (i.e. not including full path)? Just thinking about the need for indirect to have the other file open.

[drawkcaB]

I want to use the full path name so that Sheet A can be moved around on the network, to a different folder, copied, etc. while still ensuring that it's still "pointing" to Sheet B for information. If Sheet B is open, and in Sheet A

=VLOOKUP(W53,Transmitters.xls!headerArray, x, FALSE)

Where W53 is the extracted portion of the Sheet A file to ensure the proper row is referenced in worksheet "Header" in Sheet B. Provided Sheet B (i.e. Transmitters.xls) is open, will Sheet A always link to Sheet B regardless of copy/moving Sheet A? If that's the case I'd should work fine since as you mentioned Sheet B is always open because of INDIRECT anyways...

[Cheeky Charlie]

Yes, it would still work.

Indirect doesn't 'know' anything about the result of its calculation - i.e. indirect is never going to work out when the end point of its calculated reference is moved that it needs to change the reference - that's why:
(a) a lot of people use it for 'fixing' a reference =indirect("C3") will always return the result of C3 regardless of how many rows and columns you insert/remove.
(b) it slows down Excel because it has to recalculate every time you do something just in case the thing you've changed is the thing that formula points to

hth

[drawkcaB]

Well then it looks like implementing this was easier that I thought it would be. Thank you very much!