Re: How to dynamically change the series range of a chart ?
- From: "John Coleman" <jcoleman@xxxxxxxxxxxxxx>
- Date: 4 Oct 2006 11:36:08 -0700
Here is a formula that matches your original problem description:
=OFFSET(OFFSET(Sheet1!$B$1,Sheet1!$A$1-1,0),0,0,Sheet1!$A$2-Sheet1!$A$1+1)
If you name this say "DataRange" then it will refer to the part of
column B between row number stored in A1 and row number stored in A2.
Then if you get the chart series to point to "DataRange" as indicated
on Walkenbach's website - it should work.
Are you sure that you entered the formula in correctly? I just pasted
what you gave into my name dialogue box and had no problem.
Hope that helps
-John Coleman
ptek wrote:
Hi John,
I'm having problems with the example on the link you gave me.
Excel reports a "The formula you typed contains an error" when I try to
enter the
=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1! $A:$A)-1)
Accordingly with the Excel help, OFFSET should take 5 parameters and
not the 4 of the example. But it seems not the cause of the problem,
since even when add the missing parameter or I try the
=OFFSET(C3,2,3,1,1) refered on the help, the same error message
appears. I cannot get OFFSET to work on this... I tried other formula,
like =SUM(Sheet1!$B$2:$B$4) which was accepted with no trouble ...
And as far I understood, OFFSET is was I needed, since it gives me a
range...
I'm using excel 2003 sp2.
John Coleman wrote:
It is possible to do it without a macro.Key ideas:
1) Series can refer to *names* rather than explicit ranges.
2) Names can refer to *formulas* - which can be made to return the
range you want to graph.
John Walkenbach's web site has a nice example of this trick:
http://j-walk.com/ss/excel/usertips/tip053.htm
You could also go the macro route of course (in which case you might
find some of Walkenbach's Excel Programming books helpful)
Hope that helps
-John Coleman
ptek wrote:
Hi,
For a chart, each series range are defined by something like :
=sheetname!$B$2:$B$25 (being the data located on column B, ranging
from row 2 to 25, as an example).
I access this (and I can change it) by manually right clicking on the
chart, selecting Source Data, and editing the above.
Is it possible to change the range in function of other cell contents?
That is, imagine I want to define the range of the serie to column B,
row 10 to 20. I would put the starting column value (10) on A1 and the
end column (20) at A2 and somehow, the =sheetname thing would be
updated to =sheetname!$B$10:$B$20 in function of the contents of A1 and
A2.
Can it be done easly ? Or is it needed a macro ? May anyone give me an
example ?
thanks
.
- Follow-Ups:
- References:
- Prev by Date: Looping through data and Populate template
- Next by Date: Re: View cell range in userform
- Previous by thread: Re: How to dynamically change the series range of a chart ?
- Next by thread: Re: How to dynamically change the series range of a chart ?
- Index(es):
Relevant Pages
|
