| 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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
search column, hyperlink, offset, substitute, match (omg)
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. "Bernie Deitrick" wrote: 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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
search column, hyperlink, offset, substitute, match (omg)
using items like (in row 15):
=SUMPRODUCT(--(LEFT($AF$111:$AF$1197,1)={"u","d"})) "Bernie Deitrick" wrote: 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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
search column, hyperlink, offset, substitute, match (omg)
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 "nastech" wrote in message ... 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. "Bernie Deitrick" wrote: 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 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
search column, hyperlink, offset, substitute, match (omg)
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(SUBS TITUTE(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. "Bernie Deitrick" wrote: 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 |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
search column, hyperlink, offset, substitute, match (omg)
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(SUBS TITUTE(CELL("address",AF15),"$",""),ROW(),"")&MIN( IF(ISERROR(AF1:AF1197),ROW(AF56:AF1197),10000))),0 ,0))),"X") "Bernie Deitrick" wrote: 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 | |||
Linear Mode
