MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Title line update

Posted by Shawn Nsdon on July 17, 2001 5:47 AM

I have a form that has a title line in it. I'd like to
set things up so that I can enter a date in one cell,
have all the remaining cells set, and the title line
set as well. Here is what the form looks like:

Cell a4: Activities for Period of June 17 - July 14
a6: =(a5+1)
a7: =(a6+1)

I'd like to be able to just enter the date in cell a5
and have Excel automatically update the header cell,
in addition to the rest of the cells. Is there a way
to do that?

Posted by Eric on July 17, 2001 6:58 AM


The following works but uses date numbers instead of date text ("6" instead of "Jun"). The formula also assumes that the last date always occurs in the same cell. Paste it into cell a4. Hope it helps.
="Activities for period of "&MONTH(A5)&"/"&DAY(A5)&" - "&MONTH(A32)&"/"&DAY(A32)

Posted by Ian on July 17, 2001 8:09 AM

Re: concatenation..extra

Here's a slightly longer idea:

="Activities for Period of "&INDEX(Months,MONTH(A5))&" "&DAY(A5)&" - "&INDEX(Months,MONTH(MAX(A5:A38)))&" "&DAY(MAX(A5:A39))

if you place the names January - December somewhere on the sheet (anywhere you can comfortably hide), select the whole range then Insert_Names_Define and call it Months (or whatever you want). this will then referenrce to the Named range. You can then hide the range.
You can change the a5:a(whatever you want).

the result should be what you want.

Any good?