Automatically insert chart name as chart title

I have a chart of master data set as its own sheet, which I'm about to start copying out to show specific series.

What I would like to do is have the chart title pick up the chart (Sheet) name automatically, so that I don't have to rename them all.

I've seen a way to return the sheet name when it's an object on a sheet, but not when it's a sheet in it's own right.

I have also found a method using VBA, but wonder whether there is a simpler route without the need for script?

Thanks in advance

J

Assuming that the chart lives in a Worksheet and not a chart sheet:

In a cell in the worksheet, enter this formula

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99)

Select the chart title, click into the formula bar, enter a = sign and then click the cell with the formula.

Now you can rename the sheet and the title of the chart will reflect the new sheet name.

See attached.

Note that the formula above will only work if the workbook has been saved, i.e. it will not work on a new, unsaved file.

Thanks for the reply, and apologies for the poor description...

It is a chart sheet, not inserted in a worksheet.

Is there another option?

Not without VBA, no, because there is no way to store data in a chart sheet. Maybe you can re-think and use a Worksheet after all? Hide all unused rows and columns, format the remainder with a gray fill and it will look very much like a chart sheet.

I'll give it a go, if not I'll revert back to using VBA...

Thanks for your help