Data Range Question

[Bob]

Hello,

Using Excel 2007.

Had to install a new HD, and ever since then am having all sorts of
"problems".

I have a chart with dates in Col B, and then data in C, D, and E.

If I type in a data range, at the pull-down menu,limited to only where
there are actual values in B, C, D, and E, it plots fine.

But if I insert a data range much bigger, the chart goes crazy; either
blank, or nonsense lines, etc.

I want to pick a much larger data range, even though much of it is
blank, so that when values are actually
put in them, it will plot and update automatically.

Apparently it is happy only when the selected range actually has some data.

It never used to be like this.
Some setting must have changed.

Any idea what might be happening ?

Thanks,
Bob

Thanks,
Bob

 

[Ron Rosenfeld]

Hello,

Using Excel 2007.

Had to install a new HD, and ever since then am having all sorts of
"problems".

I have a chart with dates in Col B, and then data in C, D, and E.

If I type in a data range, at the pull-down menu,limited to only where
there are actual values in B, C, D, and E, it plots fine.

But if I insert a data range much bigger, the chart goes crazy; either
blank, or nonsense lines, etc.

I want to pick a much larger data range, even though much of it is
blank, so that when values are actually
put in them, it will plot and update automatically.

Apparently it is happy only when the selected range actually has some data.

It never used to be like this.
Some setting must have changed.

Any idea what might be happening ?

Thanks,
Bob

Thanks,
Bob

I don't know why you are having these problems; or what could be different about your original setup vs your current setup. BUT, when faced with the problem you describe -- an expanding data range as I add more data -- I use a named range that automatically adjusts to the amount of data being entered. Of course, the method I will describe applies only to data ranges where there are no blanks in the data.

For example, I have a chart that plots values of entities called B4, C1, H2 and MaxDiff against a set of labels which happen to be dates. I have these dynamic names defined:

ChtLabels =OFFSET(Data!$A$1,1,0,COUNT(Data!$A:$A),1)
ChtValuesB4 =OFFSET(Data!ChtLabels,0,3)
ChtValuesC1 =OFFSET(Data!ChtLabels,0,1)
ChtValuesH2 =OFFSET(Data!ChtLabels,0,2)
ChtValuesMaxDiff =OFFSET(Data!ChtLabels,0,4)

Notes:
ChtLabels has a row offset of 1 so as to exclude the first cell which contains a label
Since dates in excel are stored as numbers, and since the label for that column is text, I used the COUNT function. If your label column contained text or errors, you should use COUNTA and then subtract 1 from that count so as to exclude the text label.
The other columns in the table are defined in terms column offsets from the primary column. This ensures they will always be the same size.

Hope this helps.