Data selection in plotting a Graph 5

[shrewd]

Hi all..

subject looks pretty simple,but....
problem is ....i have a spreadsheet in which,for a set of data ,graph has to be plotted.
The cells in which data range is there is bound to a condition.
If once i input the data in the sheet,corresponding to this ,the data range changes & the range should be selected and get plotted.

Did i made it complicate.....
do suggest me solution on this issue.

Thank u

 

[prex]

A macro?

prex

http://www.xcalcs.com

: Online tools for structural design

http://www.megamag.it

: Magnetic brakes for fun rides

http://www.levitans.com

: Air bearing pads

 

[MintJulep]

Named ranges?

 

[shrewd]

may b....
i will try to put tat prob in a simple way.....
data table (X,Y);
X=1,2,3,4,5.......(incremental)
Y=0.2,0.4,0.6.....(force factor,formula dependenet)
X values keeps on incrementing by +1 and terminates at a particular value(conditional based).
Now the graph should be such tat it should select (X,Y)till X value terminates.Later cells should not be selected.

May be a macro sort,but i'm not able to give the shape....

 

[IRstuff]

There are two possible approaches.

> If you make the plot range arbitrarily large, Excel will dump all the blank lines. As you add data to the actual valid range Excel will add the new points to the plot.

> If you copy the last line and insert the copy just before the original last line, Excel will expand the range automatically in the plot. Then enter the new data into the current last line.

TTFN

FAQ731-376

 

[cummings54]

Look at setting up dynamic range names. The key is to use the offset function. For example suppose you have data in in cells a1:a26 and you would like to plot the last 10 values in column A. Then, create a formula name, say ChartData, where the refers to box is =OFFSET(Sheet1!$A$1,COUNT(Sheet1!$A:$A)-10,0,10,1). Now the series formula in your chart uses the formula name as follows:
=SERIES(,,'Dynamic Range Example.xls'!ChartData,1)
Anytime you add more data to colum A your chart will change automatically.

 

[joerd]

Define two names (via Insert/Name/Define, or Ctrl-F3), for example x and y, with the following formula (instead of a static reference like Sheet1!$A$2:$A$5):
x: [tt]=OFFSET($A$2,0,0,COUNT($A:$A),1)[/tt]
y: [tt]=OFFSET($B$2,0,0,COUNT($A:$A),1)[/tt]
This assumes that your x-range starts at A2, and the y-range at B2, modify as you need.
Then, the series in your chart should have as x values range x, and y values range y, so you would type for the series formula:
[tt]=SERIES(,Sheet1!x,Sheet1!y,1)[/tt]

that should do it. I think that Jon Peltier has something on this as well, google for dynamic range names and charts.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[electricpete]

A star for joerd. You are a real guru. Who'd of thunk you could assign a name to a formula instead of a range.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

My apologies for not acknowledging cummings54 who had a similar suggestion.

Thinking about it some more, I guess I have heard of that. On this forum someone posted the Chart Formula spreadsheet by Stephen Bullen.... generates a graph from a formula types into a spreadsheet without ever using any numerical data in cells. You can adjust the x-axis range and the number of points effortlessly. Seems kind of mystical to me, but now I remember it was built on the princple of named formulas.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[shrewd]

Thanks buddies.....
the show has come to an end....Infact i was bit unfamiliar with dynamic activities in excel,in the initial stages..but anyhow now it was pretty clear...
All ur help are greatly apreaciated.
especially for jeord,whose direction to turn to Jon Peltier ......it was really awesome.

Thank u all....