Named Range for chart data using ROW and INDIRECT

Hi All,

I am in the process of creating a sheet that has a dynamic chart by using a named range for the data source.

Formula:

Please Login or Register  to view this content.

I can use the named range "data" in formulas and as the data source for a chart.

I would like to generate a range of integers to use as axis labels. For this particular application, the labels should be integers, starting at 2. To this end, I created a named range like so;

Formula:

Please Login or Register  to view this content.

I can use the named range"xAxis_Labels" in formulas successfully, but when I try and use it to label a set of data, or as a data source in a chart, I get the error:

A formula in this worksheet contains one or more invalid references......

Any ideas?

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Sample uploaded..

Hi,

You need to add the sheet name for context
xAxis_Labels=ROW(INDIRECT("Sheet1!2:"&COUNT(Data)))

Thanks Don, that's fixed it.

Odd that the sheet name isn't required in the spreadsheet formulas - you live and learn!


Just for the sake of robustness, I've created a named range for the sheet name:

Formula:

Please Login or Register  to view this content.

and then used that in the other named range:

Formula:

Please Login or Register  to view this content.

so that if I change the name of the sheet at any point it won't fall over..

Thanks again for your help.

In a worksheet, the context is inherently that sheet unless otherwise specified. A chart is effectively its own type of sheet- even if it has been embedded in a worksheet- that has no concept of rows or columns, so you have to tie any range to a specific worksheet.