Chart Y-axis definition. Limitation for Maximum characters ?

  • Thread starter Thread starter Hari
  • Start date Start date
H

Hari

Hi,

I encountered a strange problem (I believe every problem poser must be
saying so.....)

I was defining the range of y axis value in a Chart. The cells used in
ranges were not together and hence in disparate places.

So with Ctrl pressed I would select the cells I want. This way the name of
the worksheet was also coming in the chart defining range. I had around 8
data points and hence 8 different cells.

Now when selecting the cells one by one when I got to the 7th one Excel
wasnt selecting it. I mean I used to select it and then put a comma and then
click on the 7th cell and again a comma and then the 8th ( and the final
datapoint) cell.

After this I went to some other data series and added some new ranges.

When I went to my original data series I saw that though I had specified 8
data points it wasnt specifying those 8 properly. I think one data point it
was skipping and there was no comma between 6th and 7th. I redid this
series taking care that all the 8 were specified properly. but after working
on some other chart aspects when I went to his series again the same problem
was cropping up.

I struggled with this for couple more times and then thought that may be the
space where we define the Y axis range for chart has a Maximum specification
for number of characters.

I then went back to the sheet and changed the name of the worksheet to a
small one.(The name of the worksheet was actually quite big initially -->
might be having 28 or 29 characters including spacebar) Now, when I built
my chart I could do it without any problem.

Hence my doubt is whether there is a limit to maximum characters in the
chart defining range which caused this problem..

Regards,
Hari
India
 
Hari -
Hence my doubt is whether there is a limit to maximum characters in
the chart defining range which caused this problem..
Click to expand...

That's exactly what the issue is. The series formula itself is allowed
1024 characters, but each of the four parts (name, x values, y values,
and plot order) are only allotted something less than 256 characters (a
waste, since plot order can never exceed 3 characters plus the comma
before it).

The workaround is to set aside a contiguous worksheet range that links
to the scattered cells in the worksheet.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Hi Jon,

Thanx a lot for your reply.

Just for clarification regarding "The workaround is to set aside a
contiguous worksheet", Im a little new to this thing ,hence wanted to be
completely sure that this is what u mean.

Suppose I have 20 noncontiguous cells do u suggest that in another corner of
worksheet I create 20 contiguous cells which are respectively linked to 20
noncontiguous cells.I am asking because I thought it would be sort of
cumbersome.

I mean is it possible using Indirect function or something else store the
addresses of all those 20 noncontiguous cells in one cell itself and then
use this cell reference for Y axis. ( Im a little new to indirect function
etc so dont know its potential , hence suggesting something probably
foolish)

Regards,
Hari
India
 
Back
Top