my question is below, i'm re-asking as i was unable to make it work even with
the other file open...but maybe my formula is incorrect.
can you elaborate on what the formula would look like?


Don's Response:
Indirect can, IF the other file is OPEN
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tami" wrote in message
...
I have a file that has a sheet/tab for each department e.g. 85,86,87,88
etc.
and each tab has sales info for all the items in that dept.

In another file i have an item (dept#, class, style, etc) and want to look
up the sales info in the other file....but my lookup formula must know
which
sheet to look on.

INDIRECT function doesn't work as it can't do external file references,
correct?
thx.

ALWAYS stay in the ORIGINAL thread for responses. ALWAYS!!!

=VLOOKUP(E14,INDIRECT("'[book2]"&D14&"'!$A$2:$D$9"),2,0)
where d14 contains the tab title such as 89
or
=VLOOKUP(E15,INDIRECT("'[book2]"&ROW(A89)&"'!$A$2:$D$9"),2,0)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tami" wrote in message
...
my question is below, i'm re-asking as i was unable to make it work even
with
the other file open...but maybe my formula is incorrect.
can you elaborate on what the formula would look like?


Don's Response:
Indirect can, IF the other file is OPEN
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tami" wrote in message
...
I have a file that has a sheet/tab for each department e.g. 85,86,87,88
etc.
and each tab has sales info for all the items in that dept.

In another file i have an item (dept#, class, style, etc) and want to
look
up the sales info in the other file....but my lookup formula must know
which
sheet to look on.

INDIRECT function doesn't work as it can't do external file references,
correct?
thx.