Offset() Reduce.

  • Thread starter Cactus [ÏÉÈËÇò]
  • Start date
C

Cactus [ÏÉÈËÇò]

I build a variational length record sheet.
that record length is
Sheet1!B1="=Countif(A:A,">0")"

that record Range is ("A3:A" & B1),
How to convert the String into Range?

this is fail case.
Sheet1!B3="=AVERAGE("A3:A" & B1)"
this case is pass.
Sheet1!B3="=AVERAGE(OFFSET(A3,0,0,B1,1))"

I want to know more method.

thanks.
 
B

Bob Phillips

Is this what you want

"=AVERAGE(""A3:A" & Range("B1") & """)"

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
C

Cactus [ÏÉÈËÇò]

Thank Bob


Public Function Eval(expr As String) As Variant
Eval = Evaluate(expr)
End Function


=AVERAGE(Eval("A3:A"&B1))
or
=Eval("AVERAGE(A3:A" & B1 &")")





"Bob Phillips"
 
Top