vlookup function

  • Thread starter Thread starter Brian
  • Start date Start date
B

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
 
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!
 
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
 
Back
Top