was wondering if it is possible to have a formula tell you the next line
number / the first occurance of a letter, "x", in one column? thanks

The MATCH function returns the first occurrence of a value so if the data was
in A1:A200 and the first occurence of "X" was in row 55, MATCH returns 55. I
am assuming we are comparing a cell value and not part of a string.

=MATCH("X",A1:A200,0)

Is this what you require?

"nastech" wrote:

was wondering if it is possible to have a formula tell you the next line
number / the first occurance of a letter, "x", in one column? thanks

Hi, finally back. I think that is what looking for. not that quick at some
formula's though, tried:

=IF(ISNA(MATCH("X",$AU$68:$AU$1097,0)),"",MATCH("X ",$AU$68:$AU$1097,0))

to get rid of na error, worked, but when put an "x" in the column, got an
off answer, wrong line returned.

part2: was working on tool to make it into a hyperlink to that line, might
be able to figure it out, with INDIRECT? thanks

"Toppers" wrote:

The MATCH function returns the first occurrence of a value so if the data was
in A1:A200 and the first occurence of "X" was in row 55, MATCH returns 55. I
am assuming we are comparing a cell value and not part of a string.

=MATCH("X",A1:A200,0)

Is this what you require?

"nastech" wrote:

was wondering if it is possible to have a formula tell you the next line
number / the first occurance of a letter, "x", in one column? thanks

p.s.: got the using of the first row, but was trying to exclude other than
capital "X", by using EXACT. was able to use EXACT in combination with other
functions before, but not sure if have quite right now. get a NA error with
this as well.

=IF(ISNA(MATCH("X",$AU$1:$AU$1097,0)),"",MATCH(EXA CT($AU$1:$AU$1097,"X"),$AU$1:$AU$1097,0))


"Toppers" wrote:

The MATCH function returns the first occurrence of a value so if the data was
in A1:A200 and the first occurence of "X" was in row 55, MATCH returns 55. I
am assuming we are comparing a cell value and not part of a string.

=MATCH("X",A1:A200,0)

Is this what you require?

"nastech" wrote:

was wondering if it is possible to have a formula tell you the next line
number / the first occurance of a letter, "x", in one column? thanks

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

--------------------------------

"Toppers" wrote:

The MATCH function returns the first occurrence of a value so if the data was
in A1:A200 and the first occurence of "X" was in row 55, MATCH returns 55. I
am assuming we are comparing a cell value and not part of a string.

=MATCH("X",A1:A200,0)

Is this what you require?

"nastech" wrote:

was wondering if it is possible to have a formula tell you the next line
number / the first occurance of a letter, "x", in one column? thanks

Don't know ! .... but if you still require it, the following UDF will return
the row containing first occurence of value in selected range.

=xFind(A4:A100,"X")


Function xFind(ByVal rng As Range, findval As String)
Dim v As Variant
Dim r As Long
v = rng
For r = LBound(v, 1) To UBound(v, 1)
If v(r, 1) = findval Then
xFind = r + rng.Row - 1
Exit Function
End If
Next r
End Function

"nastech" wrote:

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

--------------------------------

"Toppers" wrote:

The MATCH function returns the first occurrence of a value so if the data was
in A1:A200 and the first occurence of "X" was in row 55, MATCH returns 55. I
am assuming we are comparing a cell value and not part of a string.

=MATCH("X",A1:A200,0)

Is this what you require?

"nastech" wrote:

was wondering if it is possible to have a formula tell you the next line
number / the first occurance of a letter, "x", in one column? thanks

Thank you, will give that a try. Have been working on the item much. have
95% of answer that works:

=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)

note: W13 is column where want cursor to offset to, 13 is row that formula
is in.
$BY$14 is reference to repeat on MATCH, to get hyperlink to display row
going to.
could not get hyperlink to accept MATCH string in place of $BY$14, "yet"
anyways.
other working on, if can get EXACT "X" to work.. vs. small "x" 's... no prob


I have a short script I was having trouble with, can I have some help with?
thanks.


"Toppers" wrote:

Don't know ! .... but if you still require it, the following UDF will return
the row containing first occurence of value in selected range.

=xFind(A4:A100,"X")


Function xFind(ByVal rng As Range, findval As String)
Dim v As Variant
Dim r As Long
v = rng
For r = LBound(v, 1) To UBound(v, 1)
If v(r, 1) = findval Then
xFind = r + rng.Row - 1
Exit Function
End If
Next r
End Function

"nastech" wrote:

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

--------------------------------

"Toppers" wrote:

The MATCH function returns the first occurrence of a value so if the data was
in A1:A200 and the first occurence of "X" was in row 55, MATCH returns 55. I
am assuming we are comparing a cell value and not part of a string.

=MATCH("X",A1:A200,0)

Is this what you require?

"nastech" wrote:

was wondering if it is possible to have a formula tell you the next line
number / the first occurance of a letter, "x", in one column? thanks

in case anyone wanted to know how to hyperlink down to next occurance of a
character/s in a column, e.g.: "dn" to next.. bookmark you choose:

(is a long formula, but works, address $A468 is cell formula in, $AA$3 is
number of cells size of screen..)

=HYPERLINK(IF(ISNA(INDEX(ROW(OFFSET($AX468:$AX$195 6,1,0))-ROW(OFFSET($AX468,1,0)),MATCH(TRUE,OFFSET($AX468:$ AX$1956,1,0)="dn",0))),"","#"&CELL("address",OFFSE T(INDIRECT(SUBSTITUTE(SUBSTITUTE(CELL("address",$A 468),"$",""),ROW(),"")&INDEX(ROW(OFFSET($AX468:$AX $1956,1,0)),MATCH(TRUE,OFFSET($AX468:$AX$1956,1,0) ="dn",0))),$AA$3,0))),"dn")

"nastech" wrote:

was wondering if it is possible to have a formula tell you the next line
number / the first occurance of a letter, "x", in one column? thanks

Cntrl-Shift-Enter as an array formula