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 :
Check_this
Heading
Retry_this
Create three names for A2:A4:
Any_data
Common_data
Raw_data
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.

Advertisements

About Patrick O'Beirne, spreadsheet auditor

Patrick provides consultancy and training in spreadsheet development, auditing / testing and model review; and the Excel 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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s