Question regarding lookup

[Susan]

I am trying to create a lookup in one workbook to another workbook, here is
my problem:

in my other workbook, called ORANGE there are 52 sheets (one for each week).
I want to be able to enter a date 07-27-08 and be able to lookup the
coordinating sheet in workbook ORANGE called 07-27 and give me the value in
cell c2 of that sheet in workbook titled "ORANGE".

Can someone help me with this formula please.

Thank you!

 

[Gary''s Student]

If both workbooks are opened, you can use indirect:

In a cell A1, enter 07-27-08 as text. In another cell, enter:

=INDIRECT("'[Orange.xls]" & LEFT(A1,5) & "'" & "!$C$2")

 

[Susan]

Is there a way to do this without both workbooks opened?

If both workbooks are opened, you can use indirect:

In a cell A1, enter 07-27-08 as text. In another cell, enter:

=INDIRECT("'[Orange.xls]" & LEFT(A1,5) & "'" & "!$C$2")

--
Gary''s Student - gsnu200794

I am trying to create a lookup in one workbook to another workbook, here is
my problem:

in my other workbook, called ORANGE there are 52 sheets (one for each week).
I want to be able to enter a date 07-27-08 and be able to lookup the
coordinating sheet in workbook ORANGE called 07-27 and give me the value in
cell c2 of that sheet in workbook titled "ORANGE".

Can someone help me with this formula please.

Thank you!

 

[John C]

Assumptions: Your ORANGE workbook is open and in the same folder as your
lookup workbook.
The date you enter is in cell A1 of your lookup workbook.
The tabs in ORANGE workbook are all number mm-dd (with both month and day
having preceding zeroes if single digit).

This is your formula:

=INDIRECT("'[ORANGE.xls]"&TEXT(MONTH(A1),"00")&"-"&TEXT(DAY(A1),"00")&"'!C2")

tested and working.