Array reference in =ROWS() function
 

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

--
Gary''s Student - gsnu200729

Array reference in =ROWS() function
 

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)

Gary''s Student wrote:

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

--
Gary''s Student - gsnu200729

--

Dave Peterson

Array reference in =ROWS() function
 

Thank you Dave. I guess I need more coffee and less benadryl.
--
Gary''s Student - gsnu200729


"Dave Peterson" wrote:

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)

Gary''s Student wrote:

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

--
Gary''s Student - gsnu200729

--

Dave Peterson