|
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 |
Chart title = Worksheet Name?
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 B1:D1 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" wrote in message ervers.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 |
Chart title = Worksheet Name?
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" wrote in message ... 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 B1:D1 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" wrote in message ervers.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 |
Chart title = Worksheet Name?
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" wrote in message ... 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" wrote in message ... 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 B1:D1 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" wrote in message ervers.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 |
Chart title = Worksheet Name?
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" wrote in message ervers.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" wrote in message ... 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" wrote in message ... 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 B1:D1 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" wrote in message ervers.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 |
| All times are GMT +1. The time now is 03:52 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com