Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default Auto insert sheetname into chart title?


Hi

I'm using Excel 2003

I have several charts on a worksheet.

At the end of each month I copy the sheet to become the first worksheet
in the workbook, delete the data and rename the sheet for the following
month.

All my charts have a chart title e.g "Total income for April 2010". The
sheetname is April 2010. When I copy this sheet to become "May 2010" I
then have to manually edit all the chart titles on that sheet.

I wondered if there is a way to reference the sheetname in the chart
title so that when I rename the sheet the chart titles automatically
update.

Thanks for your time.

Ian
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default Auto insert sheetname into chart title?

On 5/8/2010 5:26 PM, Ian R wrote:

Hi

I'm using Excel 2003

I have several charts on a worksheet.

At the end of each month I copy the sheet to become the first worksheet
in the workbook, delete the data and rename the sheet for the following
month.

All my charts have a chart title e.g "Total income for April 2010". The
sheetname is April 2010. When I copy this sheet to become "May 2010" I
then have to manually edit all the chart titles on that sheet.

I wondered if there is a way to reference the sheetname in the chart
title so that when I rename the sheet the chart titles automatically
update.

Thanks for your time.

Ian


I don't think there's a way to reference a sheet name in a chart title,
but you may want to consider using a macro (VBA).

For example, the macro below will put the title "Total Income for April,
2010" in the chart title for Chart 1 and Chart 2 in the sheet named
"April, 2010." Next month, change the sheet name to "May, 2010," use
EditReplace to change every occurrence of the word "April" to "May" in
the macro, and run it. If you have charts on more than one sheet, you
can adjust the sheet names as necessary.

Sheets("April, 2010").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartTitle.Text = "Total Income for April, 2010"
Sheets("April, 2010").Select
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartTitle.Text = "Total Income for April, 2010"

Hope this helps.
Bob Ryan
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default Auto insert sheetname into chart title?

Get the sheetname into a cell in the worksheet using a FORMULA.

e.g. pick G1

In G1 enter this formula exactly as written.

=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255

That returns the sheetname to G1

Then make your chart title a formula ="Total Income for " & G1


Gord Dibben MS Excel MVP


On Sat, 8 May 2010 22:26:43 +0100, Ian R wrote:


Hi

I'm using Excel 2003

I have several charts on a worksheet.

At the end of each month I copy the sheet to become the first worksheet
in the workbook, delete the data and rename the sheet for the following
month.

All my charts have a chart title e.g "Total income for April 2010". The
sheetname is April 2010. When I copy this sheet to become "May 2010" I
then have to manually edit all the chart titles on that sheet.

I wondered if there is a way to reference the sheetname in the chart
title so that when I rename the sheet the chart titles automatically
update.

Thanks for your time.

Ian


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default Auto insert sheetname into chart title?

Gord - I learned something new, so thank you. However, i couldn't get
what you described to work. I can make the chart title equal to the cell
containing the sheet name, but I wasn't able to add the text as you
described. Any ideas?


On 5/8/2010 10:19 PM, Gord Dibben wrote:
Get the sheetname into a cell in the worksheet using a FORMULA.

e.g. pick G1

In G1 enter this formula exactly as written.

=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)

That returns the sheetname to G1

Then make your chart title a formula ="Total Income for "& G1


Gord Dibben MS Excel MVP


On Sat, 8 May 2010 22:26:43 +0100, Ian wrote:


Hi

I'm using Excel 2003

I have several charts on a worksheet.

At the end of each month I copy the sheet to become the first worksheet
in the workbook, delete the data and rename the sheet for the following
month.

All my charts have a chart title e.g "Total income for April 2010". The
sheetname is April 2010. When I copy this sheet to become "May 2010" I
then have to manually edit all the chart titles on that sheet.

I wondered if there is a way to reference the sheetname in the chart
title so that when I rename the sheet the chart titles automatically
update.

Thanks for your time.

Ian



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,522
Default Auto insert sheetname into chart title?

Just amend your cell formula to
="Total Income for " &
MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Bob Ryan" wrote in message
...
Gord - I learned something new, so thank you. However, i couldn't get what
you described to work. I can make the chart title equal to the cell
containing the sheet name, but I wasn't able to add the text as you
described. Any ideas?


On 5/8/2010 10:19 PM, Gord Dibben wrote:
Get the sheetname into a cell in the worksheet using a FORMULA.

e.g. pick G1

In G1 enter this formula exactly as written.

=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)

That returns the sheetname to G1

Then make your chart title a formula ="Total Income for "& G1

Gord Dibben MS Excel MVP


On Sat, 8 May 2010 22:26:43 +0100, Ian wrote:


Hi

I'm using Excel 2003

I have several charts on a worksheet.

At the end of each month I copy the sheet to become the first worksheet
in the workbook, delete the data and rename the sheet for the following
month.

All my charts have a chart title e.g "Total income for April 2010". The
sheetname is April 2010. When I copy this sheet to become "May 2010" I
then have to manually edit all the chart titles on that sheet.

I wondered if there is a way to reference the sheetname in the chart
title so that when I rename the sheet the chart titles automatically
update.

Thanks for your time.

Ian






  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default Auto insert sheetname into chart title?

Got it, and then realized I can put the text that will never change in
one cell, the sheet name formula in another cell and either use the
concatenate function or "&" to combine the two, which would then feed
the chart title. Thank you.
Bob

On 5/9/2010 9:05 AM, Don Guillett wrote:
Just amend your cell formula to
="Total Income for " &
MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert String Between Dates in Chart Title RyanH Excel Worksheet Functions 5 October 28th 08 09:49 PM
Automatically update SheetName in workbook sub if SheetName changes [email protected] Excel Discussion (Misc queries) 3 February 29th 08 05:33 PM
change chart title with auto filter Aja Charts and Charting in Excel 1 July 16th 05 06:34 AM
Pasting Objects into Chart title and Axis title Sam Charts and Charting in Excel 1 June 6th 05 08:50 PM
Insert contents of a cell into a title of a chart greg stenzel Charts and Charting in Excel 2 February 21st 05 12:19 AM


All times are GMT +1. The time now is 02:01 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"