Is it possible to have dynamic range in Series function

[a_83]

Suppose one my chart has this formula:

=SERIES(,,Sheet1!$B$5:$B$11,1)

Is it possible to have the range(B5:B11)to be dynamic ie the function should pick up values at runtime.

Also is there any solution without macro code i.e only excel formulas.

 

[Hui]

a_83

Yes, but not how you have done it

To make a range dynamic you must use a Named Range,

Typically like

MyRange: =offset(B1,A1,0,A2,1)

where A1 is the Start Row offset from A1 and A2 is the length of the range

and then use the named range in the Chart Series

=Series(,,MyRange,1)


For more info have a read of : http://chandoo.org/wp/2009/10/15/dynamic-chart-data-series/


Beware that you can't use the word "chart" as a named range or part of a range in a Chart in Excel 2007.

 

[a_83]

Hui

thanks for your reply

I tried the solution but its not helping me..

for e.g this the table

Name Value

a 5

b 10

c 20

Total 35

Now rows will be getting inserted after c.

Is there any way that we can dynamic range from "a" row and one row above "Total".

 

[Hui]

Did you read: http://chandoo.org/wp/2009/10/15/dynamic-chart-data-series/


Can you please let us knwo what cells you are working in eg: D10:E14 etc

 

[a_83]

Yes I went through the post....

This post helps when we are inserting rows in a table..But in my case as i said in earlier post need to have dynamic range from "a" row to 1 row above "Total".

In the sheet I am using this formula:

=SERIES('Sheet1'!$B$13:$F$13,'Sheet1'!$B$16:$C$17,'Sheet1'!$H$16:$H$17,1)


I want to have $H$16:$H$17 to be dynamic range.