Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I reference certain rows within array formulae? | Excel Worksheet Functions | |||
function to reference all of pivottable data field array | Excel Worksheet Functions | |||
Cannot Reference "Array" in Subtotal Function | Excel Worksheet Functions | |||
how do i reference multiple rows/columns with one function? | New Users to Excel | |||
how do i reference multiple rows/columns with one function? | New Users to Excel |