dynamic data range chart

**helpplease!** · 11-13-2007, 05:51 PM

Hi,

A B
X Y
X Y
X Y
X Y
"" ""
"" ""
...

here is my question: the values in col A are "" or a date value and the values of col B are "" or a currency value. how would i make a chart to have a data range to not include any blanks?

for example above, the chart data range would be A1:B4

there cannot be "" within the range.

any help appreciated,

thanks,

helpplease!

**oldchippy** · 11-13-2007, 05:57 PM

Hi,

May be this link can help?

http://peltiertech.com/Excel/Charts/...umnChart1.html

**helpplease!** · 11-13-2007, 07:59 PM

Hi,

A B
X N
X Y
X Y
X Y
"" ""
"" ""
...

thanks for the link... i tried that but i can't enter the formula for the values:

=OFFSET(Sheet1!$B$1,1,0,COUNTA(Sheet1!$B:$B)-1,1)

because i have alot of "" which i do not want included. i tried using:

=sheet1!B1:INDEX(sheet1!B1:B100,MATCH(2,1/(sheet1!B1:B100<>"")))

but although the name was defined, the chart was incorrect when the series formula was entered.

any ideas?

**vikasbhandari** · 11-14-2007, 06:59 AM

Originally Posted by helpplease!

Hi,

A B
X N
X Y
X Y
X Y
"" ""
"" ""
...

thanks for the link... i tried that but i can't enter the formula for the values:

=OFFSET(Sheet1!$B$1,1,0,COUNTA(Sheet1!$B:$B)-1,1)

because i have alot of "" which i do not want included. i tried using:

=sheet1!B1:INDEX(sheet1!B1:B100,MATCH(2,1/(sheet1!B1:B100<>"")))

but although the name was defined, the chart was incorrect when the series formula was entered.

any ideas?

If you can afford to have any error in your data...just try putting =NA() in your data in place of ""...#NA is ignore by the chart!