| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
search column, hyperlink, offset, substitute, match (omg)
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(SU BSTITUTE(CELL("address",W14),"$",""),ROW(),"")&MAT CH("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)),"",SUBSTITUT E(SUBSTITUTE(CELL("address",W14),"$",""),ROW(),"") &MATCH("X",$AU$1:$AU$1097,0)) works |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
search column, hyperlink, offset, substitute, match (omg)
=HYPERLINK("#"&CELL("address",OFFSET(INDIRECT(IF(I SNA(MATCH("X",$AU$1:$AU$1097,0)),"",SUBSTITUTE(SUB STITUTE(CELL("address",W14),"$",""),ROW(),"")&MATC H("X",$AU$1:$AU$1097,0))),0,0)),"x")
HTH, Bernie MS Excel MVP "nastech" wrote in message ... 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(SU BSTITUTE(CELL("address",W14),"$",""),ROW(),"")&MAT CH("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)),"",SUBSTITUT E(SUBSTITUTE(CELL("address",W14),"$",""),ROW(),"") &MATCH("X",$AU$1:$AU$1097,0)) works |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
search column, hyperlink, offset, substitute, match (omg)
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(I SNA(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 "Bernie Deitrick" wrote: =HYPERLINK("#"&CELL("address",OFFSET(INDIRECT(IF(I SNA(MATCH("X",$AU$1:$AU$1097,0)),"",SUBSTITUTE(SUB STITUTE(CELL("address",W14),"$",""),ROW(),"")&MATC H("X",$AU$1:$AU$1097,0))),0,0)),"x") HTH, Bernie MS Excel MVP "nastech" wrote in message ... 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(SU BSTITUTE(CELL("address",W14),"$",""),ROW(),"")&MAT CH("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)),"",SUBSTITUT E(SUBSTITUTE(CELL("address",W14),"$",""),ROW(),"") &MATCH("X",$AU$1:$AU$1097,0)) works |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
search column, hyperlink, offset, substitute, match (omg)
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(SUBS TITUTE(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) "Bernie Deitrick" wrote: =HYPERLINK("#"&CELL("address",OFFSET(INDIRECT(IF(I SNA(MATCH("X",$AU$1:$AU$1097,0)),"",SUBSTITUTE(SUB STITUTE(CELL("address",W14),"$",""),ROW(),"")&MATC H("X",$AU$1:$AU$1097,0))),0,0)),"x") HTH, Bernie MS Excel MVP "nastech" wrote in message ... 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(SU BSTITUTE(CELL("address",W14),"$",""),ROW(),"")&MAT CH("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)),"",SUBSTITUT E(SUBSTITUTE(CELL("address",W14),"$",""),ROW(),"") &MATCH("X",$AU$1:$AU$1097,0)) works |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
search column, hyperlink, offset, substitute, match (omg)
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(SUBS TITUTE(CELL("address",W14),"$",""),ROW(),"")&MATCH ("X",$AU$1:$AU$1109,0)),$W$1,0))),"X") "Bernie Deitrick" wrote: =HYPERLINK("#"&CELL("address",OFFSET(INDIRECT(IF(I SNA(MATCH("X",$AU$1:$AU$1097,0)),"",SUBSTITUTE(SUB STITUTE(CELL("address",W14),"$",""),ROW(),"")&MATC H("X",$AU$1:$AU$1097,0))),0,0)),"x") HTH, Bernie MS Excel MVP "nastech" wrote in message ... 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(SU BSTITUTE(CELL("address",W14),"$",""),ROW(),"")&MAT CH("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)),"",SUBSTITUT E(SUBSTITUTE(CELL("address",W14),"$",""),ROW(),"") &MATCH("X",$AU$1:$AU$1097,0)) works |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
search column, hyperlink, offset, substitute, match (omg)
Array enter - using Ctrl-Shift-Enter
=HYPERLINK(IF(ISNA(MIN(IF(ISERROR(AU1:AU1109),ROW( AU1:AU1109),100))),"","#"&CELL("address",OFFSET(IN DIRECT(SUBSTITUTE(SUBSTITUTE(CELL("address",W14)," $",""),ROW(),"")&MIN(IF(ISERROR(AU1:AU1109),ROW(AU 1:AU1109),100))),$W$1,0))),"Error") HTH, Bernie MS Excel MVP "nastech" wrote in message ... 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(SUBS TITUTE(CELL("address",W14),"$",""),ROW(),"")&MATCH ("X",$AU$1:$AU$1109,0)),$W$1,0))),"X") "Bernie Deitrick" wrote: =HYPERLINK("#"&CELL("address",OFFSET(INDIRECT(IF(I SNA(MATCH("X",$AU$1:$AU$1097,0)),"",SUBSTITUTE(SUB STITUTE(CELL("address",W14),"$",""),ROW(),"")&MATC H("X",$AU$1:$AU$1097,0))),0,0)),"x") HTH, Bernie MS Excel MVP "nastech" wrote in message ... 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(SU BSTITUTE(CELL("address",W14),"$",""),ROW(),"")&MAT CH("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)),"",SUBSTITUT E(SUBSTITUTE(CELL("address",W14),"$",""),ROW(),"") &MATCH("X",$AU$1:$AU$1097,0)) works |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
search column, hyperlink, offset, substitute, match (omg)
Thankyou very much.. (works great) these two items worked out to be a pretty
neat trick. "Bernie Deitrick" wrote: Array enter - using Ctrl-Shift-Enter =HYPERLINK(IF(ISNA(MIN(IF(ISERROR(AU1:AU1109),ROW( AU1:AU1109),100))),"","#"&CELL("address",OFFSET(IN DIRECT(SUBSTITUTE(SUBSTITUTE(CELL("address",W14)," $",""),ROW(),"")&MIN(IF(ISERROR(AU1:AU1109),ROW(AU 1:AU1109),100))),$W$1,0))),"Error") HTH, Bernie MS Excel MVP "nastech" wrote in message ... 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(SUBS TITUTE(CELL("address",W14),"$",""),ROW(),"")&MATCH ("X",$AU$1:$AU$1109,0)),$W$1,0))),"X") "Bernie Deitrick" wrote: =HYPERLINK("#"&CELL("address",OFFSET(INDIRECT(IF(I SNA(MATCH("X",$AU$1:$AU$1097,0)),"",SUBSTITUTE(SUB STITUTE(CELL("address",W14),"$",""),ROW(),"")&MATC H("X",$AU$1:$AU$1097,0))),0,0)),"x") HTH, Bernie MS Excel MVP "nastech" wrote in message ... 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(SU BSTITUTE(CELL("address",W14),"$",""),ROW(),"")&MAT CH("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)),"",SUBSTITUT E(SUBSTITUTE(CELL("address",W14),"$",""),ROW(),"") &MATCH("X",$AU$1:$AU$1097,0)) works |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
search column, hyperlink, offset, substitute, match (omg)
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" wrote: Array enter - using Ctrl-Shift-Enter =HYPERLINK(IF(ISNA(MIN(IF(ISERROR(AU1:AU1109),ROW( AU1:AU1109),100))),"","#"&CELL("address",OFFSET(IN DIRECT(SUBSTITUTE(SUBSTITUTE(CELL("address",W14)," $",""),ROW(),"")&MIN(IF(ISERROR(AU1:AU1109),ROW(AU 1:AU1109),100))),$W$1,0))),"Error") HTH, Bernie MS Excel MVP "nastech" wrote in message ... 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(SUBS TITUTE(CELL("address",W14),"$",""),ROW(),"")&MATCH ("X",$AU$1:$AU$1109,0)),$W$1,0))),"X") "Bernie Deitrick" wrote: =HYPERLINK("#"&CELL("address",OFFSET(INDIRECT(IF(I SNA(MATCH("X",$AU$1:$AU$1097,0)),"",SUBSTITUTE(SUB STITUTE(CELL("address",W14),"$",""),ROW(),"")&MATC H("X",$AU$1:$AU$1097,0))),0,0)),"x") HTH, Bernie MS Excel MVP "nastech" wrote in message ... 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(SU BSTITUTE(CELL("address",W14),"$",""),ROW(),"")&MAT CH("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)),"",SUBSTITUT E(SUBSTITUTE(CELL("address",W14),"$",""),ROW(),"") &MATCH("X",$AU$1:$AU$1097,0)) works |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
search column, hyperlink, offset, substitute, match (omg)
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) ) -- HTH, Bernie MS Excel MVP "nastech" wrote in message ... 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" wrote: Array enter - using Ctrl-Shift-Enter =HYPERLINK(IF(ISNA(MIN(IF(ISERROR(AU1:AU1109),ROW( AU1:AU1109),100))),"","#"&CELL("address",OFFSET(IN DIRECT(SUBSTITUTE(SUBSTITUTE(CELL("address",W14)," $",""),ROW(),"")&MIN(IF(ISERROR(AU1:AU1109),ROW(AU 1:AU1109),100))),$W$1,0))),"Error") HTH, Bernie MS Excel MVP "nastech" wrote in message ... 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(SUBS TITUTE(CELL("address",W14),"$",""),ROW(),"")&MATCH ("X",$AU$1:$AU$1109,0)),$W$1,0))),"X") "Bernie Deitrick" wrote: =HYPERLINK("#"&CELL("address",OFFSET(INDIRECT(IF(I SNA(MATCH("X",$AU$1:$AU$1097,0)),"",SUBSTITUTE(SUB STITUTE(CELL("address",W14),"$",""),ROW(),"")&MATC H("X",$AU$1:$AU$1097,0))),0,0)),"x") HTH, Bernie MS Excel MVP "nastech" wrote in message ... 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(SU BSTITUTE(CELL("address",W14),"$",""),ROW(),"")&MAT CH("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)),"",SUBSTITUT E(SUBSTITUTE(CELL("address",W14),"$",""),ROW(),"") &MATCH("X",$AU$1:$AU$1097,0)) works |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional Format as a MACRO | Excel Worksheet Functions | |||
| Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
| Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
| Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
| search value in a table and sum another column | Excel Worksheet Functions | |||
Hybrid Mode
