Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 3rd 12, 01:05 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2009
Posts: 77
Default Data Range Question

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

  #2   Report Post  
Old October 3rd 12, 02:16 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2010
Posts: 1,045
Default Data Range Question

On Wed, 03 Oct 2012 08:05:08 -0400, Bob wrote:

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.




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
question regarding external data range EdStevens Excel Programming 5 April 9th 08 09:37 AM
Loop QUESTION to end of data range... mniccole Excel Worksheet Functions 2 December 4th 06 10:25 PM
Loop QUESTION to end of data range... mniccole Excel Worksheet Functions 2 December 4th 06 06:19 PM
Data Range Question. ChuckF Excel Worksheet Functions 3 April 11th 06 07:29 PM
Data Validation Range Question Steph[_3_] Excel Programming 2 December 9th 04 08:59 PM


All times are GMT +1. The time now is 07:42 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017