Find ROW(INDIRECT()) wo the column letters

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hi, had it before, cannot remember setup for formula to indirect a reference
to a row, without having to enter a column, i.e.:

=ROW(INDIRECT($AP$11))<ROW($AP$208)

AP11 = AP208, answer = false (works),
but need to be able to just enter the row number in AP11. maybe did not
even use INDIRECT..
 
If you're entering a number in AP11, couldn't you use:

=$ap$11<208
or
=$ap$11<row($ap$208)

I'm not sure why you'd want to use:
=ROW(INDIRECT($AP$11&":"&$AP$11))
 
=ROW(INDIRECT("$AP"&$AP$11))<ROW($AP$208)

thanks, tried that again and it worked. At first think had auto calculation
turned off.
(else note: am trying to externalize a row number, to be able to modify,
thanks)
 
thanks! =$ap$11<row($ap$208)

was the simple answer to what had used previously, couldn't remember what it
was while trying to fix a new problem. was trying to externalize a setting
for a row number for a larger formula. the following worked for this problem:

=ROW(INDIRECT("$AP"&$AP$11))<ROW($AP$208)

had tried that, but think had my auto calculation setting turned off.
works now, thanks.
in-case anyone wanted to know, was using in a hyperlink to the next instance
of a name (after) the one I had hyperlinked / jumped to, to find the next
"copy".

for $AO16: column where want cursor to go, row formula currently resides in.
read-out / friendly name is quantity of instances.

=HYPERLINK(IF
ISNA(INDEX(ROW(INDIRECT("$AO"&$AS$16):$AO$1221)-ROW(INDIRECT("$AO"&$AS$16))+1,MATCH(TRUE,INDIRECT("$AO"&$AS$16):$AO$1221=$AO16,0))),""
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBSTITUTE(CELL("address",$AO16),"$",""),ROW(),"")&INDEX(ROW(INDIRECT("$AO"&$AS$16):$AO$1221),MATCH(TRUE,INDIRECT("$AO"&$AS$16):$AO$1221=$AO16,0))),$Y$2,0))),IF
ISNA(INDEX(ROW(INDIRECT("$AO"&$AS$16):$AO$1221)-ROW(INDIRECT("$AO"&$AS$16))+1,MATCH(TRUE,INDIRECT("$AO"&$AS$16):$AO$1221=$AO16,0))),0,
SUMPRODUCT(--(INDIRECT("$AO"&$AS$16):AO$1221=$AO16))))

-----------------
 
Back
Top