help with weird data range

[Davoucci]

I have created a chart for 2 series of data. Both data series are on
one summary page and are in 1 column. Kinda looks like:
Col. A-----Col. B
Actual-----119.54
Est.-------120.00
Actual-----132.13
Est.-------140.00
Actual-----89.56
Est.-------95.00
etc...

What I need help with is naming a dynamic data range (as the length of
Col. C varies) in a way that series 1 would only collect data from the
"Actual" rows and series 2 would collect data from the "Est." rows. I
can do the basic type
"=OFFSET(Summary!$E$6,0,0,COUNTA(Summary!$E:$E),1)" but that collects
actual and est. as one series. Any help would be greatly appreciated.
Thanks in advance!

 

[Frank Kabel]

Hi
you'll need a helper range which transposes your data to a format which
can be used by the chart wizard:
lets say we use column E and F for this (row one gets a heading row
with E1: 'Actual' and F1: 'Est'). Now enter the following in E2:
=IF(OFFSET($A$1,(ROW()-2)*2,0)<>"",OFFSET($A$1,(ROW()-2)*2,0),"")

in F1 enter
=IF(OFFSET($A$2,(ROW()-2)*2,0)<>"",OFFSET($A$2,(ROW()-2)*2,0),"")

for creating a dynamic range you may use
=OFFSET($E$1,0,0COUNT($E:$E))