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.

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.

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("add ress",D14))

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=534444

Hi Harlan,

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

Thanks, you guys out here are great.

Les

"Harlan Grove" wrote in message
oups.com...
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.

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" wrote in message
...

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("add ress",D14))

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile:
http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=534444

I've had a rethink!

Try

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

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=534444