Array reference in =ROWS() function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gary''s Student - 13 Jun 2007 17:45 GMT

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??

Signature

Gary''s Student - gsnu200729

Reply to this Message

Dave Peterson - 13 Jun 2007 17:51 GMT

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)

> I enter:
>
[quoted text clipped - 28 lines]
> --
> Gary''s Student - gsnu200729

Signature

Dave Peterson

Reply to this Message

Gary''s Student - 13 Jun 2007 18:11 GMT

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

Signature

Gary''s Student - gsnu200729

> I think you'd want something like:
> =ROWS(INDIRECT("2:"&$E$1))
[quoted text clipped - 40 lines]
> > --
> > Gary''s Student - gsnu200729

Reply to this Message