search column, hyperlink, offset, substitute, match (omg)

[Guest]

060711 search column, hyperlink, offset, substitute, match (omg)

trying to get a hyperlink to go to designated errors (column where "X"
appears if there is an error). using the last 2 items, it works as a worker
cell & hyperlink refering to it.

trying to get this to work, (trying to combine the bottom 2 items), get "too
few arguments". (note: to work, W14 is "column you want link to go to, & 14
is the line that formula is currently in). thanks.

=HYPERLINK("#"&CELL("address",OFFSET(SUBSTITUTE(SUBSTITUTE(CELL("address",W14),"$",""),ROW(),"")&MATCH("X",$AU$1:$AU$1097,0),0,0)),"x") trying

these work separately
=HYPERLINK("#"&CELL("address",OFFSET(INDIRECT($CH$14),0,0)),"x") works
=IF(ISNA(MATCH("X",$AU$1:$AU$1097,0)),"",SUBSTITUTE(SUBSTITUTE(CELL("address",W14),"$",""),ROW(),"")&MATCH("X",$AU$1:$AU$1097,0)) works

 

[Bernie Deitrick]

=HYPERLINK("#"&CELL("address",OFFSET(INDIRECT(IF(ISNA(MATCH("X",$AU$1:$AU$1097,0)),"",SUBSTITUTE(SUBSTITUTE(CELL("address",W14),"$",""),ROW(),"")&MATCH("X",$AU$1:$AU$1097,0))),0,0)),"x")

HTH,
Bernie
MS Excel MVP

 

[Guest]

Hi, thanks! just that I got that far cracks me up.. wouldn't believe how
many variations of items tried to get 2 bottom examples to work, mostly a
brain teaser
but still: should save me 10-15 minutes of finding/correcting errors after
downloads, when need to save time most. this work paying off anyways.
(RECOMMEND MS PUT CHECKBOX? / AUTOMATE HYPERLINKS TO GO TO EXACT LINE:
OFFSET.. SO WORK NOT BOUNCING AROUND EVERY WHERE,, ALL THE TIME.)

secondary:
- small confict did work around, EXACT("X" to skip small x's

- hyperlink part 2 (friendly name): can maybe identify the line number
with e.g.:
=hyperlink(criteria,MATCH("X",$AU$1:$AU$1102,0) does not work, but using an
absolute cell: $BY$14 (to the Match), does:

=HYPERLINK("#"&CELL("address",OFFSET(INDIRECT(IF(ISNA(MATCH("X",$AU$1:$AU$1097,0)),"",
SUBSTITUTE(SUBSTITUTE(CELL("address",W13),"$",""),ROW(),"")
&MATCH("X",$AU$1:$AU$1097,0))),$W$1,0)),$BY$14)


- small problem with location in sheet, for hyperlinks.
for offset, rows: moving up would use a: -1
for moving down (using a refernce cell, as below): 32 lines / $W$1

- FOR in document hyperlinks, have devised:
=HYPERLINK(IF(ROW($A$314)<=ROW($A274),
"#"&CELL("address",OFFSET($A$314,-1,1)),
"#"&CELL("address",OFFSET($A$314,$W$1,1))),"A")

small note: if above reference / title line, 2nd offset has $W$1, and:
"<=" up front
to resolve conflicts up/down with title lines on same view. Leads to:

Double Situation Question: is there a way to resolve the row / location, if
to only cut formula in half or simplify; B: to use hyper-links in header,
where cannot choose direction (MUST HAVE 2 LINKS: ONE FOR UP, ONE FOR DOWN)


THNKS

 

[Guest]

Hi, thanks, found following to work (or finding Error "X" in spreadsheet):

=HYPERLINK(IF(ISNA(MATCH("X",$AU$1:$AU$1079,0)),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBSTITUTE(CELL("address",W14),"$",""),ROW(),"")&MATCH("X",$AU$1:$AU$1079,0)),$W$1,0))),"X")

or enter this in place of: "" to hyperlink to diff location, if no errors:
"#"&CELL("address",OFFSET($A$314,-1,1)),

again, in order to work: W14 column W where you want hyperlink to go to,
and 14 is row where this formula resides.
$W$1 is amount of lines offset for size of your screen..
can use formula in W1 to auto adjust your freezpanes (for e.g. 31 rows
offset):
=48-CELL("row",$A$17)

 

[Guest]

Hi, was wondering how to include, if an error exists in a column.. was trying
iserror & #div/0! thanks, using: for find "x"

=HYPERLINK(IF(ISNA(MATCH("X",$AU$1:$AU$1109,0)),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBSTITUTE(CELL("address",W14),"$",""),ROW(),"")&MATCH("X",$AU$1:$AU$1109,0)),$W$1,0))),"X")

 

[Bernie Deitrick]

Array enter - using Ctrl-Shift-Enter

=HYPERLINK(IF(ISNA(MIN(IF(ISERROR(AU1:AU1109),ROW(AU1:AU1109),100))),"","#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBSTITUTE(CELL("address",W14),"$",""),ROW(),"")&MIN(IF(ISERROR(AU1:AU1109),ROW(AU1:AU1109),100))),$W$1,0))),"Error")

HTH,
Bernie
MS Excel MVP

 

[Guest]

Thankyou very much.. (works great) these two items worked out to be a pretty
neat trick.

 

[Guest]

If anyone cares, slight modification made to exclude lines >greater than or
<less than certain values: thanks again

FIND ERROR IN COLUMN: (by Bernie Deitrick, XL MVP) Cntrl-Shift-Enter bc15
refers to: =CELL("row",$A$68
=HYPERLINK(IF(OR(ROW()<=$BC$15,ROW()>=$BH$15,ISNA(MIN(IF(ISERROR(AF1:AF1210),ROW(AF1:AF1210),100)))),"","#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBSTITUTE(CELL("address",AF15),"$",""),ROW(),"")&MIN(IF(ISERROR(AF1:AF1212),ROW(AF1:AF1212),100))),$W$1,0))),"X")


XXXXXXXXXXXXXXXXXXXXXXXXX

 

[Bernie Deitrick]

Actually, I forgot to change the ,100 to a value higher than 1212 - use 10000 or some other large
value in this expression (both places)

MIN(IF(ISERROR(AF1:AF1210),ROW(AF1:AF1210),100))
should be something like
MIN(IF(ISERROR(AF1:AF1210),ROW(AF1:AF1210),100000))

 

[Guest]

hi, will do that, but what is it for? may figure it out, but...
ran into what "may"? be another problem, am using a header row where totals
of quantities are listed for that column.

that cell receives same error, and hyperlink goes to that "first" error,
instead of what intended. that is what my attempt at isolating ROWs out was
for. Is there a different fix? thanks.

 

[Guest]

using items like (in row 15):
=SUMPRODUCT(--(LEFT($AF$111:$AF$1197,1)={"u","d"}))

 

[Bernie Deitrick]

The 100 was meant to return a value that is larger than any row number that may be returned by the
IF function in the array formula, so that rows that don't have errors or Xs in them will not be
included in the return.

HTH,
Bernie
MS Excel MVP

 

[Guest]

trying to make the ROW() thing work, because may have settings, above in that
column, such as "u" for up, that otherwise conflicts with other work samle we
did, for e.g.: (not working) thanks.

=HYPERLINK(IF(OR(ROW()<=$BC$15,ROW()>=$BH$15,ISNA(MATCH("X",$AF$1:$AF$1197,0))),""
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBSTITUTE(CELL("address",W16),"$",""),ROW(),"")&MATCH("U",$AF$1:$AF$1197,0)),$W$1,0))),"x")


note: found can leave line returns in cells, for working on formula's, does
not seem to interfere with formula.

 

[Guest]

hi, I thought I had it working. guesse the work for finding an error is the
more important item. is there a way to exclude lines above a certain point.
was using the following, but the ROW()'s exclusion might not be correct.

=HYPERLINK(IF(OR(ROW()<=$BC$15,ROW()>=$BH$15,ISNA(MIN(IF(ISERROR(AF1:AF1197),ROW(AF1:AF1197),10000)))),""
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBSTITUTE(CELL("address",AF15),"$",""),ROW(),"")&MIN(IF(ISERROR(AF1:AF1197),ROW(AF56:AF1197),10000))),0,0))),"X")