Reference names in external workbook

[cdawg]

Hello,
I need to access names in an external workbook from the current one. Assuming both workbooks are open, can I do this?

For example:

The external workbook (Xbook) has the following Names defined:
Table1 - Cells D4:K12
RowID - Cell C6 (which contains a value of 4)

In the Current workbook there is a row of data (cells B2:B10) which corresponds to the 1st row in the external table. Soooo I would like to perform an HLOOKUP referring to the lookup value in the current table and the table array and row index number in the external table. The resulting lookup would be something like: HLOOKUP(B2, Xbook.Table1, Xbook.RowID, 0) Is this feasible by using Excel formulas only?

Thanks in advance

 

[xenou]

Yes.
It is easiest if you use the mouse as you enter the formula. Start typing in the cell:
=HLookup(B2,

Then browse to the external file and select the range D4:K12 and enter a comma, and select the range C6, and finish the formula:

,0)

Excel should fill in the requisite details.

You will probably find that the formula looks something like this:
=HLookup(B2,[XBook.xls]!Table1,[XBook.xls]!RowID,0)

Vlookup should even work with the external workbook closed, but its faster if its open, and also easier to enter the formula if you have both open as you can use your mouse for entering ranges. Often Excel recognizes the range as a named range and will convert the range you selected to its name automatically.

 

[cdawg]

Thanks a bunch - that does work. You are correct that Excel did recognize the array and rowid variable from the external workbook and pull them over to the current formula. It did create the formula without the brackets but with quotes as there are spaces in the filename.

Now for another (and last) twist. Suppose the filename that I want to look up is in a cell in the current workbook. I think the INDIRECT function is intended for this use, but I am having some trouble with the syntax. Particularly doesn't INDIRECT need the brackets and the single quotes?

Again with the above example: if the name of the file Xbook is in cell F1 and is stored as [Xbook.xls], I think the formula should be something like:

HLOOKUP(G2,INDIRECT("'"&F1&"!Table1"),INDIRECT("'"&F1&"'RowID"),0)

but I keep getting #REFs. Any help here?

Thanks so much.

 

[xenou]

I find this works:
=HLOOKUP(G2,INDIRECT(F1&"!Table1"),INDIRECT(F1&"!RowID"),0)

Where F1 has:
[XBook.xls]Sheet1


Notes:
1)
It's a little mysterious with names as they are supposed to work at a workbook-level, so theoretically we should not need the sheet name. But Indirect seems to want it (in my quick test, anyway). So your formula worked when I added Sheet1 to the workbook name. I'm not sure what that means.

2)
Indirect will not work with closed workbooks. Beware.

3)
You need the single quotes if there is a space in the name (of the workbook or worksheet). I never name workbook or worksheets with spaces - so I never have to bother with that. I don't know what the rules about brackets are. I think they must go around workbook names in such references to external workbooks, and the file extension should be included as part of the workbook name inside the brackets (If the workbook is closed I guess you'd need the whole file path, but indirect won't work with closed workbooks anyway, in this case).

4)
Excel will name Tables Table1, Table2, Table3 ... etc. when you add Tables using the data menu. I would avoid this name for your ranges.

 

[cdawg]

Thanks a lot. Your solution did work and it will help me greatly!!!

I did need to modify it to take into account external spreadsheets with spaces in the name - so I am now using this in the HLOOKUP:

=HLOOKUP(G2,INDIRECT("'" & $F$1&"INPUTS'" &"!Array1"),INDIRECT("'" & $F$1& "INPUTS'"& "!RowID"),0) where INPUTS is a sheet name.

Again, your help is much appreciated.