Using a named range as a chart title

[ajwillshire]

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

 

[Andrew Poulsom]

The same happens in Excel 2000. Use an explicit cell reference.

 

[ajwillshire]

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]

Use a different name for the cell.

 

[Andrew Poulsom]

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]

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]

You need a different name again. Title_Decomp for example would be OK.