Using a named range as a chart title

ajwillshire

New Member
Joined
Mar 4, 2009
Messages
31
Hi,

I'm using Excel 2007 and I have a chart where I want the title to link to a cell.

It works if the formula is "=Sheet1!$a$1"

but if I name that cell "ChartTitle" then I can't get it to work.

i.e.,

"=Sheet1!ChartTitle" doesn't work.

I've also tried putting the workbook name in the formula and various other options, but it keeps telling me there's an error.

Any ideas what I'm doing wrong (or if there's a bug)?

Thanks a lot,
Andrew
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

ajwillshire

New Member
Joined
Mar 4, 2009
Messages
31
Thanks Andrew.

It was in the context of restoring a chart title which I removed in VBA while sending a chart to PowerPoint so I wanted to restore the title as a formula automatically using named refs in order to minimise the chances of bugs creeping in.

I think what I'll do is use

ActiveWorkbook.Names("ChartTitle").RefersTo

to return the string "=Sheet1!$A$1" which I can then put in the chart title using VBA.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,142
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Use a different name for the cell. ;)
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

Yes Rory, this worked in Excel 2000:

=Sheet1!ChtTitle

although Excel immediately changed the sheet reference to the name of the workbook. The name without the sheet reference doesn't work for a Chart Title although it does work for a shape.
 

ajwillshire

New Member
Joined
Mar 4, 2009
Messages
31
Thanks Rory, but I was actually using the name

"ChTitle_Decomp"

as the named range when the problem arose and I couldn't get round it at all.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,142
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You need a different name again. Title_Decomp for example would be OK.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,158
Messages
5,600,054
Members
414,357
Latest member
Gemma_R

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top