Chart title = Worksheet Name?

[William Halper]

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

 

[Bernard Liengme]

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 Liengme]

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

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

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

 

[bhalper]

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

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

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

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

 

[Tushar Mehta]

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

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

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

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

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

 

[Bernard Liengme]

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

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

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

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

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