I wanted to make my bar chart dynamic. For this purpose I have used =A1:INDEX(A2:A60;COUNTA(A2:A60)) formula. I gave a name to this formula using name manager. When I use this formula in my X-AXIS LABEL RANGE (in bar chart -> select data), I am getting error The formula you typed contain an error. But my formula is working well in cell.
Please suggest, where I am doing it wrong. Thank you.
When you enter a named range into a chart series dialog or the label dialog, you must precede the range name with either the sheet name or the file name.
Consider the following screenshot:
See how the formula for the x axis labels is entered as =Sheet1!chtLabels
The formula for the range name is
=Sheet1!$A$1:INDEX(Sheet1!$A$2:$A$60,COUNTA(Sheet1!$A$2:$A$60))
Similar for the series definitions. The range name alone is not sufficient. It has to be preceded by the sheet name (or the file name).
