vlookup function

[Brian]

I have the following vlookup function,

=VLOOKUP(A6,'2004 Atlantic S&D Expenses.xls'!
AtlanticTotal,14,FALSE)

The table from which cell A6 is being looked up from is in
a different file.

The Atlantic S&D Expenses.xls file has several
worksheets. Each worksheet is the same but has differnt
data for, so worksheet 1 is Atlantic, worksheet 2 has
Pacific data.
I wish to make the table in the vlookup function variable,
so I would use Data validation to change AtlanticTotal to
another named range in the Atlantic S&D file, say Pacific.

Can this be done? Can you use named range references from
different files in the vlookup function?

Thanks

 

[Frank Kabel]

Hi
if the other file is open and your sheet name is in cell B6 try
=VLOOKUP(A6,INDIRECT("'[2004 Atlantic S&D Expenses.xls]" & B6 & "'!
A1:N1000"),14,FALSE)

Note: This does NOT work if '2004 Atlantic S&D Expenses.xls' is closed!

 

[Peo Sjoblom]

If the other file is open you can use INDIRECT for
this
Assume D1 is the cell where you select the ranges

=VLOOKUP(A6,INDIRECT("'2004 Atlantic S&D Expenses.xls'!"&D1),2,0)

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom