INDIRECT / Setting to vary a range?

N

nastech

060903: INDIRECT / Setting to vary a range?
hi, am looking for way to vary a range from a single setting / cell..
to vary the number of columns used, in a range; have examples:

would like to be able to extend CR:CV (5 columns), to 10 columns.. CR9:DA9
to allow row nine to be dynamic to any row. thanks

=IF(OR(CQ9="",CV9=""),"",AVERAGE(CR9:CV9))

have idea: (but does not allow for different rows?) / don't know how to
modify for use from a single cell..
or is there another way to answer problem? possibly INDIRECT?
=SUBSTITUTE(SUBSTITUTE(CELL("address",$CV$9),"$",""),ROW(),"")
 
N

nastech

with: (wrong with row 11 after indirect)
=IF(OR(CQ11="",CU11=""),"",AVERAGE(CQ11:INDIRECT($DX$3)11))

using 1 cell to be able to adjust CV (with location in 3rd row, use 3, and
3 disappears/ only "CV" shows):
=SUBSTITUTE(SUBSTITUTE(CELL("address",$CV$3),"$",""),ROW(),"")
 
Top