Thread: Text X Axis Labels on Bar/Line charts (2007)

kolsky, this would be much easier to analyse if you post a sample file that shows the problem you describe. Where does the error occur? When you enter a formula in a cell?

So this has nothing to do with charting, has it? the cells just happen to be chart labels, but the formula that creates them has an error.

post a file. Make it small.

Thanks so much for your help...

The problem is in the labeling of the X-axis.
I posted the formula used to create the named region for the X-axis labels.

Here is the file...
Note that if you remove the "1" from the "1a" the label "9" appears...

The point of this exercise is to create a label from two columns, as in:

| 1 | 4/5 |
| 2 | 4/12 |

will result in:

....1.........2
...4/5.....4/12

i.e.: A1&CHAR(10)&TEXT(B1, "m/d")

Thank again!

You are constructing the range name "Sprints" with this formula

=OFFSET('_RBU Pgm'!$A$11,0,0,COUNT('_RBU Pgm'!$A:$A),1)

That works fine as long as the values in column A contain only numbers.

But then you start replacing the numers in column A with text, using a formula like
=IF(ISBLANK('Sprint Data'!A12),"",'Sprint Data'!A12&"a")

These text values will not be picked up by the Count() function in the Sprints name definition. (Note that in the example you posted the X axis labels are missing the label for the last data point. That is because only 8 cells now have numbers. Sprints does not cover all your labels.

As soon as you have fille the range with texts, there are no numbers left that the Count() formula in the Sprints dynamic range can detect, and the height parameter of the OFFSET() is set to 0. Therefore the error.

Replace Count() with Counta(), which will count any cell content, not just numbers, and all is hunky dory.

So, Sprints
=OFFSET('_RBU Pgm'!$A$11,0,0,COUNTA('_RBU Pgm'!$A:$A),1)

cheers