Find ROW(INDIRECT()) wo the column letters
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(SUBS TITUTE(CELL("address",$AO16),"$",""),ROW(),"")&IND EX(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))))
-----------------
"Dave Peterson" wrote:
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))
nastech wrote:
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..
--
Dave Peterson
|