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

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

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

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

Hello John,

I've been trying the example you gave me. This time, I managed to
define the name with no problem. So, I defined the Datarange as
=OFFSET(OFFSET(Sheet1!$B$1,Sheet1!$A$1-1,0),0,0,Sheet1!$A$2-Sheet1!$A$1+1)
as you said, and the error which occured the other time following the
example on the webpage no longer happened.

Anyway, if now I select Insert/Name/Define and click on the Datarange
name, the following "Refers" is displayed as on the pcture :
http://www.filelodge.com/files/1043/excelOdd1.jpg

I find a bit odd the =" =OFFSET at the begining ...

But when trying to change the data range using the Datarange i've got
the following error :
http://www.filelodge.com/files/1043/excelOdd2.jpg

Please note that I didn't found the =SERIES( thing on the dialog ...

Strange errors ...

As for the example on tthe webpage link, I tried a few times just
copy-pasting and the error still happens.





John Coleman wrote:
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

Hello John,

I've been trying the example you gave me. This time, I managed to
define the name with no problem. So, I defined the Datarange as
=OFFSET(OFFSET(Sheet1!$B$1,Sheet1!$A$1-1,0),0,0,Sheet1!$A$2-Sheet1!$A$1+1)
as you said, and the error which occured the other time following the
example on the webpage no longer happened.

Anyway, if now I select Insert/Name/Define and click on the Datarange
name, the following "Refers" is displayed as on the pcture :
http://www.filelodge.com/files/1043/excelOdd1.jpg

I find a bit odd the =" =OFFSET at the begining ...

But when trying to change the data range using the Datarange i've got
the following error :
http://www.filelodge.com/files/1043/excelOdd2.jpg

Please note that I didn't found the =SERIES( thing on the dialog ...

Strange errors ...

As for the example on tthe webpage link, I tried a few times just
copy-pasting and the error still happens.





John Coleman wrote:
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

Hello,

You need to get rid of the starting =" as well as a matching " at the
end of the refers to. For some reason Excel is treating the reference
of the name as a *string* (which happens to be of a formula) and not
the formula itself. Just go to the Insert/Name/Define box and manually
remove those 3 symbols - that should (I hope) fix the problem (The
chart is naturally objecting to being passed a string and not a range).

Hope that helps

-John Coleman

ptek wrote:
Hello John,

I've been trying the example you gave me. This time, I managed to
define the name with no problem. So, I defined the Datarange as
=OFFSET(OFFSET(Sheet1!$B$1,Sheet1!$A$1-1,0),0,0,Sheet1!$A$2-Sheet1!$A$1+1)
as you said, and the error which occured the other time following the
example on the webpage no longer happened.

Anyway, if now I select Insert/Name/Define and click on the Datarange
name, the following "Refers" is displayed as on the pcture :
http://www.filelodge.com/files/1043/excelOdd1.jpg

I find a bit odd the =" =OFFSET at the begining ...

But when trying to change the data range using the Datarange i've got
the following error :
http://www.filelodge.com/files/1043/excelOdd2.jpg

Please note that I didn't found the =SERIES( thing on the dialog ...

Strange errors ...

As for the example on tthe webpage link, I tried a few times just
copy-pasting and the error still happens.





John Coleman wrote:
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