Chart title = Worksheet Name?

I have a workbook where every sheet represents a different day. The title
of the sheet is the date e.g. 9-14-06. On each sheet is a chart whose title
is same date as on the tab, but formatted slightly differently: Thursday
<cr> September 14, 2006.

Is there a way to easily link the two so when I update the tab date, the
chart date changes also?

Thanks,
Bill Halper

I do hope someone can find a shorter way but here is mine.
Firstly, the name on the tab will not be recognized as text by Excel, so wee
need to get the sheet name into a cell and then extract year, month day
A1 (sheet name) =MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)
{works only after file has been save at least once}
B1 (year) =RIGHT(A1,4)
C1 (month) =MID(A1,1,FIND("-",A1)-1)
D1 (day)
=MID(A1,FIND("*",SUBSTITUTE(A1,"-","*",1))+1,FIND("-",SUBSTITUTE(A1,"-","*",1))-FIND("*",SUBSTITUTE(A1,"-","*",1))-1)
E1 (date) =DATE(B1,C1,D1)
I know you could combine B11 into E1 but what a mess, and you could
combine A1 into that also!!
F1 (Day of the Week)
=CHOOSE(WEEKDAY(E1),"Sun","Mon","Tue","Wed","Thur","Fri","Sat") {I'll let
you type in the full names}
G1 a cell for chart title =F1&CHAR(13)&TEXT(E1,"mmmm dd, yyyy")
Enter some dummy chart name, click on that name, in Formula Bar type = and
the click on G1, you will see ='9-14-2006'!$G$1 if the sheet is named
9-14-2006. On the chart you will have a two lined title Weekname and Date in
format Monthname day, year.

done
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"William Halper" <bhalper@covad.net> wrote in message
news:b5180$45130b3e$406927f3$27104@msgid.meganewsservers.com...
>I have a workbook where every sheet represents a different day. The title
> of the sheet is the date e.g. 9-14-06. On each sheet is a chart whose
> title
> is same date as on the tab, but formatted slightly differently: Thursday
> <cr> September 14, 2006.
>
> Is there a way to easily link the two so when I update the tab date, the
> chart date changes also?
>
> Thanks,
> Bill Halper
>
>

Getting tired, first sentence should read:
Firstly, the name on the tab will not be recognized as A DATE by Excel, so
we
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> wrote in message
news:O%23ASXpd3GHA.4924@TK2MSFTNGP05.phx.gbl...
>I do hope someone can find a shorter way but here is mine.
> Firstly, the name on the tab will not be recognized as text by Excel, so
> wee need to get the sheet name into a cell and then extract year, month
> day
> A1 (sheet name) =MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)
> {works only after file has been save at least once}
> B1 (year) =RIGHT(A1,4)
> C1 (month) =MID(A1,1,FIND("-",A1)-1)
> D1 (day)
> =MID(A1,FIND("*",SUBSTITUTE(A1,"-","*",1))+1,FIND("-",SUBSTITUTE(A1,"-","*",1))-FIND("*",SUBSTITUTE(A1,"-","*",1))-1)
> E1 (date) =DATE(B1,C1,D1)
> I know you could combine B11 into E1 but what a mess, and you could
> combine A1 into that also!!
> F1 (Day of the Week)
> =CHOOSE(WEEKDAY(E1),"Sun","Mon","Tue","Wed","Thur","Fri","Sat") {I'll let
> you type in the full names}
> G1 a cell for chart title =F1&CHAR(13)&TEXT(E1,"mmmm dd, yyyy")
> Enter some dummy chart name, click on that name, in Formula Bar type = and
> the click on G1, you will see ='9-14-2006'!$G$1 if the sheet is named
> 9-14-2006. On the chart you will have a two lined title Weekname and Date
> in format Monthname day, year.
>
> done
> best wishes
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "William Halper" <bhalper@covad.net> wrote in message
> news:b5180$45130b3e$406927f3$27104@msgid.meganewsservers.com...
>>I have a workbook where every sheet represents a different day. The title
>> of the sheet is the date e.g. 9-14-06. On each sheet is a chart whose
>> title
>> is same date as on the tab, but formatted slightly differently: Thursday
>> <cr> September 14, 2006.
>>
>> Is there a way to easily link the two so when I update the tab date, the
>> chart date changes also?
>>
>> Thanks,
>> Bill Halper
>>
>>
>
>

That almost works, but having the A1 cell tied to the file name causes all
of the sheets to update to the date shown on the most recently changed
worksheet. There's got to be a way to read the name property of the
worksheet and store it in a cell...perhaps using VBA?

Thanks,
Bill Halper


"Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> wrote in message
news:%23fgvPyd3GHA.3508@TK2MSFTNGP03.phx.gbl...
> Getting tired, first sentence should read:
> Firstly, the name on the tab will not be recognized as A DATE by Excel,
> so we
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> wrote in message
> news:O%23ASXpd3GHA.4924@TK2MSFTNGP05.phx.gbl...
>>I do hope someone can find a shorter way but here is mine.
>> Firstly, the name on the tab will not be recognized as text by Excel, so
>> wee need to get the sheet name into a cell and then extract year, month
>> day
>> A1 (sheet name) =MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)
>> {works only after file has been save at least once}
>> B1 (year) =RIGHT(A1,4)
>> C1 (month) =MID(A1,1,FIND("-",A1)-1)
>> D1 (day)
>> =MID(A1,FIND("*",SUBSTITUTE(A1,"-","*",1))+1,FIND("-",SUBSTITUTE(A1,"-","*",1))-FIND("*",SUBSTITUTE(A1,"-","*",1))-1)
>> E1 (date) =DATE(B1,C1,D1)
>> I know you could combine B11 into E1 but what a mess, and you could
>> combine A1 into that also!!
>> F1 (Day of the Week)
>> =CHOOSE(WEEKDAY(E1),"Sun","Mon","Tue","Wed","Thur","Fri","Sat") {I'll let
>> you type in the full names}
>> G1 a cell for chart title =F1&CHAR(13)&TEXT(E1,"mmmm dd, yyyy")
>> Enter some dummy chart name, click on that name, in Formula Bar type =
>> and the click on G1, you will see ='9-14-2006'!$G$1 if the sheet is named
>> 9-14-2006. On the chart you will have a two lined title Weekname and Date
>> in format Monthname day, year.
>>
>> done
>> best wishes
>> --
>> Bernard V Liengme
>> www.stfx.ca/people/bliengme
>> remove caps from email
>>
>> "William Halper" <bhalper@covad.net> wrote in message
>> news:b5180$45130b3e$406927f3$27104@msgid.meganewsservers.com...
>>>I have a workbook where every sheet represents a different day. The
>>>title
>>> of the sheet is the date e.g. 9-14-06. On each sheet is a chart whose
>>> title
>>> is same date as on the tab, but formatted slightly differently: Thursday
>>> <cr> September 14, 2006.
>>>
>>> Is there a way to easily link the two so when I update the tab date, the
>>> chart date changes also?
>>>
>>> Thanks,
>>> Bill Halper
>>>
>>>
>>
>>
>
>

In A1, instead of CELL("filename") use CELL("filename", A1)

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <dab47$45137396$d8665a07$32103@msgid.meganewsservers.com>,
bhalper@covad.net says...
> That almost works, but having the A1 cell tied to the file name causes all
> of the sheets to update to the date shown on the most recently changed
> worksheet. There's got to be a way to read the name property of the
> worksheet and store it in a cell...perhaps using VBA?
>
> Thanks,
> Bill Halper
>
>
> "Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> wrote in message
> news:%23fgvPyd3GHA.3508@TK2MSFTNGP03.phx.gbl...
> > Getting tired, first sentence should read:
> > Firstly, the name on the tab will not be recognized as A DATE by Excel,
> > so we
> > --
> > Bernard V Liengme
> > www.stfx.ca/people/bliengme
> > remove caps from email
> >
> > "Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> wrote in message
> > news:O%23ASXpd3GHA.4924@TK2MSFTNGP05.phx.gbl...
> >>I do hope someone can find a shorter way but here is mine.
> >> Firstly, the name on the tab will not be recognized as text by Excel, so
> >> wee need to get the sheet name into a cell and then extract year, month
> >> day
> >> A1 (sheet name) =MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)
> >> {works only after file has been save at least once}
> >> B1 (year) =RIGHT(A1,4)
> >> C1 (month) =MID(A1,1,FIND("-",A1)-1)
> >> D1 (day)
> >> =MID(A1,FIND("*",SUBSTITUTE(A1,"-","*",1))+1,FIND("-",SUBSTITUTE(A1,"-","*",1))-FIND("*",SUBSTITUTE(A1,"-","*",1))-1)
> >> E1 (date) =DATE(B1,C1,D1)
> >> I know you could combine B11 into E1 but what a mess, and you could
> >> combine A1 into that also!!
> >> F1 (Day of the Week)
> >> =CHOOSE(WEEKDAY(E1),"Sun","Mon","Tue","Wed","Thur","Fri","Sat") {I'll let
> >> you type in the full names}
> >> G1 a cell for chart title =F1&CHAR(13)&TEXT(E1,"mmmm dd, yyyy")
> >> Enter some dummy chart name, click on that name, in Formula Bar type =
> >> and the click on G1, you will see ='9-14-2006'!$G$1 if the sheet is named
> >> 9-14-2006. On the chart you will have a two lined title Weekname and Date
> >> in format Monthname day, year.
> >>
> >> done
> >> best wishes
> >> --
> >> Bernard V Liengme
> >> www.stfx.ca/people/bliengme
> >> remove caps from email
> >>
> >> "William Halper" <bhalper@covad.net> wrote in message
> >> news:b5180$45130b3e$406927f3$27104@msgid.meganewsservers.com...
> >>>I have a workbook where every sheet represents a different day. The
> >>>title
> >>> of the sheet is the date e.g. 9-14-06. On each sheet is a chart whose
> >>> title
> >>> is same date as on the tab, but formatted slightly differently: Thursday
> >>> <cr> September 14, 2006.
> >>>
> >>> Is there a way to easily link the two so when I update the tab date, the
> >>> chart date changes also?
> >>>
> >>> Thanks,
> >>> Bill Halper
> >>>
> >>>
> >>
> >>
> >
> >
>
>
>

Here is a VBA solution.
After inserting a module in VBA editor and pasting this function, type
=Expt() in some cell and 'point' the chart tile at it.
best wishes

Function Expt()
temp = ActiveSheet.Name
Mydate = DateValue(temp)
Myweekday = Weekday(Mydate)
MyWeekdayName = Application.WorksheetFunction.Choose(Myweekday, "Sunday",
"Monday",_ "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")
MyDisplay = Format(Mydate, "mmmm dd, yyyy")
Expt = MyWeekdayName & Chr(13) & MyDisplay
End Function




--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"bhalper" <bhalper@covad.net> wrote in message
news:dab47$45137396$d8665a07$32103@msgid.meganewsservers.com...
> That almost works, but having the A1 cell tied to the file name causes all
> of the sheets to update to the date shown on the most recently changed
> worksheet. There's got to be a way to read the name property of the
> worksheet and store it in a cell...perhaps using VBA?
>
> Thanks,
> Bill Halper
>
>
> "Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> wrote in message
> news:%23fgvPyd3GHA.3508@TK2MSFTNGP03.phx.gbl...
>> Getting tired, first sentence should read:
>> Firstly, the name on the tab will not be recognized as A DATE by Excel,
>> so we
>> --
>> Bernard V Liengme
>> www.stfx.ca/people/bliengme
>> remove caps from email
>>
>> "Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> wrote in message
>> news:O%23ASXpd3GHA.4924@TK2MSFTNGP05.phx.gbl...
>>>I do hope someone can find a shorter way but here is mine.
>>> Firstly, the name on the tab will not be recognized as text by Excel, so
>>> wee need to get the sheet name into a cell and then extract year, month
>>> day
>>> A1 (sheet name) =MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)
>>> {works only after file has been save at least once}
>>> B1 (year) =RIGHT(A1,4)
>>> C1 (month) =MID(A1,1,FIND("-",A1)-1)
>>> D1 (day)
>>> =MID(A1,FIND("*",SUBSTITUTE(A1,"-","*",1))+1,FIND("-",SUBSTITUTE(A1,"-","*",1))-FIND("*",SUBSTITUTE(A1,"-","*",1))-1)
>>> E1 (date) =DATE(B1,C1,D1)
>>> I know you could combine B11 into E1 but what a mess, and you could
>>> combine A1 into that also!!
>>> F1 (Day of the Week)
>>> =CHOOSE(WEEKDAY(E1),"Sun","Mon","Tue","Wed","Thur","Fri","Sat") {I'll
>>> let you type in the full names}
>>> G1 a cell for chart title =F1&CHAR(13)&TEXT(E1,"mmmm dd, yyyy")
>>> Enter some dummy chart name, click on that name, in Formula Bar type =
>>> and the click on G1, you will see ='9-14-2006'!$G$1 if the sheet is
>>> named 9-14-2006. On the chart you will have a two lined title Weekname
>>> and Date in format Monthname day, year.
>>>
>>> done
>>> best wishes
>>> --
>>> Bernard V Liengme
>>> www.stfx.ca/people/bliengme
>>> remove caps from email
>>>
>>> "William Halper" <bhalper@covad.net> wrote in message
>>> news:b5180$45130b3e$406927f3$27104@msgid.meganewsservers.com...
>>>>I have a workbook where every sheet represents a different day. The
>>>>title
>>>> of the sheet is the date e.g. 9-14-06. On each sheet is a chart whose
>>>> title
>>>> is same date as on the tab, but formatted slightly differently:
>>>> Thursday
>>>> <cr> September 14, 2006.
>>>>
>>>> Is there a way to easily link the two so when I update the tab date,
>>>> the
>>>> chart date changes also?
>>>>
>>>> Thanks,
>>>> Bill Halper
>>>>
>>>>
>>>
>>>
>>
>>
>
>