Chart defined name oddity

I’ve seen this before but can’t remember where. It was discussed on the Excel-L list starting 15 Oct 2012 with the subject line “charting challenge: cannot change title’s (formula) reference to a worksheet cell”
Try this in Excel 2010: in a new workbook, in Sheet1, enter the following data in A1 to A4: Test series, 1, 3, 2
Create three names for A1 :
Create three names for A2:A4:
Save it as “mytest.xlsx”, select A1:A4, insert a default 2-D column chart. Now try using the named ranges.
1. Select the chart title. In the formula bar, type an = sign and point to A1. It enters =Check_this and you get an error message “The formula you typed contains an error”.
2. Change it to =Sheet1!A1 and it accepts it. Change the value in A1 and the title changes. Fine.
3. Change it to =Sheet1!Heading and it changes it to =mytest.xlsx!Heading and accepts it.
4. Change it to =mytest.xlsx!Retry_this and you get the formula error message.
5. Undo the error and proceed with the next test:
6. Select the series and in the formula bar you should see: =SERIES(Sheet1!$A$1,,Sheet1!$A$2:$A$4,1)
7. Change Sheet1!$A$1 to Sheet1!Check_This and it changes it to “Test series”, the value in A1. Same if you change it to Sheet1!Retry_this.
8. Change Sheet1!$A$1 to =Sheet1!Heading and it changes it to =mytest.xlsx!Heading and accepts it.
9. Change Sheet1!$A$2:$A$4 to Sheet1!Any_data and it changes it to mytest.xlsx!Any_data and accepts it.
10. Change Sheet1!$A$2:$A$4 to Sheet1!common_data and pressing Enter has no effect, and no error message appears. Same for Sheet1!Raw_data

So in summary, you cannot base a chart element on a name that begins with R or C.

About Patrick O'Beirne, Excel/VBA developer

Patrick provides consultancy and training in spreadsheet automation, data analysis, testing and model review; and the Excel spreadsheet auditing addin XLtest
This entry was posted in Excel/VBA and tagged , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s