Array reference in =ROWS() function

[Guest]

I enter:

=ROWS(2:$80)

and copy it down. I get:

=ROWS(2:$80)
=ROWS(3:$80)
=ROWS(4:$80)
=ROWS(5:$80)
=ROWS(6:$80)

and see:

79
78
77
76
75

This is O.K. I want to put $80 in E1 and use the cell reference instead.
Both:

=rows(2:E1)
and
=rows(2:indirect(E1))
fail.

What is the correct syntax??

 

[Dave Peterson]

I think you'd want something like:
=ROWS(INDIRECT("2:"&$E$1))
but this will fail when you drag it down.

With the formula in A1, you could use:
=ROWS(INDIRECT(ROW()+1&":"&$E$1))

or more simply
=$e$1-row()

(but put 80 in E1--not $80)

 

[Guest]

Thank you Dave. I guess I need more coffee and less benadryl.