Question

Locked

Vlookup in Another Document

By aalegrete ·
I want to look up a value in an external excel doc using the Vlookup function. However, I want to populate the vlookup function from a second cell within the same sheet the vlookup function is located.

The vlookup function would be =IF(ISBLANK(C68),"",VLOOKUP(C68,'[September_2009_WWPL_Excel_Version.xls]ALL PRODUCTS'!B:G,2,FALSE)).

What I want to do is change the external document name in the vlookup function because I don't want to change 100s of vlookup statements every month a new price list is created.

The vlookup function I'm trying to get working is =IF(ISBLANK(C68),"",VLOOKUP(C68,'[cell("Contents",Z23)]ALL PRODUCTS'!B:G,2,FALSE)).

Where cell Z23 contains September_2009_WWPL_Excel_Version.xls

When I hit enter, I get #N/A in the cell. i can't seem to get this working.

The reason I want this is so that I can change the name of the monthly price list in one location within the sheet and have all the vlookups use the correct external excel file.

Any help is much appreciated.

This conversation is currently closed to new comments.

2 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

INDIRECT Function

by dnhak In reply to Vlookup in Another Docume ...

You can use the INDIRECT function to turn a text string into a formula, see below:

=IF(ISBLANK(C68),"",VLOOKUP(C68,INDRECT("[" & Z23 & "] ALL PRODUCTS'!B:G"),2,FALSE))

Note: the cocatenation of 3 parts below which mimics the VLOOKUP Table_array. And the inclusion of the [ and ] so you only need to put the "spreadsheet name.xls" in cell Z23.
Warning: This formula will only work if the looked up table is open. You would have to get a bit more clever to accomodate it not being open by putting the whole file path in!

Good luck...
"[" & Z23 & "] ALL PRODUCTS'!B:G"

Back to Software Forum
2 total posts (Page 1 of 1)  

Related Discussions

Related Forums