Referencing sheet1 from sheet2

[Les]

Hi I hope I can explain this easily without confusion. I have 12 sheets
each representing the months of the year ie Jan,Feb, Mar etc. Then I have a
summary sheet called Summary which I want to be able to display certain
values from one of the 12 sheets. The deciding factor will be based on what
month the current date is. I use the TODAY() function and a CHOOSE function
to display the name of the month in 3 letters in my summary sheet. Then I
want to be able to incorporate the name of the month in my formula so that I
can grab various pieces of info for the sheet that has the tab of the same
month. My problem is I use the following formula:

=IF(Apr!D14<=TODAY(),Apr!D14,"")

My problem is how do I build a function that will be able to take the 3
letter month on the summary page, concatenate the ! and the D14 so that the
above formula grabs the data on sheet Apr cell D14? Understand in using
this formula I need to be able to copy the formula when I am done so that I
can place it in 50 rows on the summary sheet.

Thank you in advance,

Les

ps Lets not complicate it to much <S>.

 

[Harlan Grove]

Les wrote...

. . . I use the TODAY() function and a CHOOSE function
to display the name of the month in 3 letters in my summary sheet. . . .

You might find TEXT(TODAY(),"mmm") simpler.

. . . Then I
want to be able to incorporate the name of the month in my formula so that I
can grab various pieces of info for the sheet that has the tab of the same
month. My problem is I use the following formula:

=IF(Apr!D14<=TODAY(),Apr!D14,"")

My problem is how do I build a function that will be able to take the 3
letter month on the summary page, concatenate the ! and the D14 so that the
above formula grabs the data on sheet Apr cell D14? Understand in using
this formula I need to be able to copy the formula when I am done so that I
can place it in 50 rows on the summary sheet.

....

=IF(INDIRECT(M&"!"&CELL("Address",D14))<=TODAY(),
INDIRECT(M&"!"&CELL("Address",D14)),"")

where M represents the 3 letter month on the summary page.

 

[Dav]

It is not entirely clear what you require. If you just wish to have the
month that the date is in returned eg if today is 1-apr-06 you want the
summary data from the sheet apr returned, but if it was 1-may-06 you
would want the data for may returned try

=INDIRECT(TEXT(MONTH(TODAY()),"mmm")&"!"&CELL("address",D14))

Regards

Dav

 

[Les]

Hi Harlan,

Looks nice!! I will give it a go tonight.

Thanks, you guys out here are great.

Les

 

[Les]

Hi Dav,

Just looking at both you guys responses briefly. I will explain more later.
I think the thing your formula doesnt consider is that if the cell address
on sheet Jan is blank then I want to bring over a blank onto sheet summary.

Thanks,

Les

 

[Dav]

I've had a rethink!

Try

=IF(ISBLANK(INDIRECT(TEXT(TODAY(),"mmm")&"!"&CELL("address",D14))),"",INDIRECT(TEXT(TODAY(),"mmm")&"!"&CELL("address",D14)))

Regards

Dav