referring to sheet in another file

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tami - 31 May 2008 15:27 GMT

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

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"Tami" <Tami@discussions.microsoft.com> wrote in message
news:E9C4A1D5-BE28-4644-8091-E440DB953054@microsoft.com...

>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.

Reply to this Message

Don Guillett - 31 May 2008 16:33 GMT

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)

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> my question is below, i'm re-asking as i was unable to make it work even
> with
[quoted text clipped - 16 lines]
>> correct?
>> thx.

Reply to this Message