Prev: In Excel how do you rearrange a list of address's in alphaeticalo
Next: custom format changes to special
From: Ian R on 8 May 2010 17:26

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
From: Bob Ryan on 8 May 2010 19:28
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
Edit>Replace 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
From: Gord Dibben on 8 May 2010 22:19
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 <nospam(a)nopsam.com> 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

From: Bob Ryan on 9 May 2010 00:23
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 R<nospam(a)nopsam.com> 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
>

From: Don Guillett on 9 May 2010 09:05
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
dguillett(a)gmail.com
"Bob Ryan" <Bob.Ryan(a)SimplyLearningExcelXSPAM.com> wrote in message
news:eAKdZ9y7KHA.5848(a)TK2MSFTNGP06.phx.gbl...
> 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 R<nospam(a)nopsam.com> 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
>>
>

 |  Next  |  Last
Pages: 1 2
Prev: In Excel how do you rearrange a list of address's in alphaeticalo
Next: custom format changes to special