Auto adjusting charts based on cells with formulas qwestion.

sergiy

New member
Joined
Aug 3, 2014
Messages
9
Reaction score
0
Points
0
Hello everyone. Please help with making auto adjusting charts. There are 2 non adjacent columns of data. Ranges where data is located (b13:b30020) and (d13:d30020). Both columns have formulas, and data displayed there is resoult of calculations based on other columns data. So both columns,if have values present,its continuos[without gaps],otherwise cell are ("") and have only formulas.
I made simple [x:y] line chart based on both columns of data. Need to make charts auto adjustable to include cells that belongs to specified range (b13:b30020),starting from b13 and till last cell that have a value present[first that is ""]. Obvious solution offset/counta didn't work,becouse excel recognize cell with formula and without value as zero. I can't format them as a table. Any ideas that could help?
i use excel 2010. generic windows laptop.
 
what is the formula for the dynamic range and what is the formula of the cells in that range
 
B13 has something like =if(a13="","",d13+m13) Then it copied all down till b30020. I defined name ''trials'' by using formula =OFFSET($B$13,0,0,COUNTA($B$13:$B$30020)-1) It didn't worked expected way. Excel see sells without data as zero. Need other formula to define range name and make chart onlysells with values in it
 
if you want to count 0 and numbers but not blanks use =COUNTIF($B$13:$B$30020,"*?") instead of count a
 
it didn't solve the problem, but you gave me an idea...ill try,then say something
 
Back
Top