060724 Match, Exact? NA error
hi, trying to make an "exact" match for the case, of a character, e.g.: "A"

where the following works, am trying to isolate capital "A"'s.
=MATCH("A",$AE$1:$AE$1299,0)

the following receives a NA error. Is there a correct way to represent?
am open to inserting IF statements, EXACT, or ROW<=().. etc.
=MATCH(EXACT($AE$1:$AE$1299,"A"),$AE$1:$AE$1299,0)


example of an EXACT, that was able to get to work in combo with other:
=SUMPRODUCT(--(EXACT(LEFT($AE$112:$AE$1300,1),"A")))

What kind of data do you have in your range?

Do the cells just contain the single letter "A" or do they contain strings
like "Address" or "adult" ?

So, if you're using MATCH then you just want the relative position of "A"
within the array AE1:AE1299 ?

Your posts always confuse me!

Biff

"nastech" wrote in message
...
060724 Match, Exact? NA error
hi, trying to make an "exact" match for the case, of a character, e.g.:
"A"

where the following works, am trying to isolate capital "A"'s.
=MATCH("A",$AE$1:$AE$1299,0)

the following receives a NA error. Is there a correct way to represent?
am open to inserting IF statements, EXACT, or ROW<=().. etc.
=MATCH(EXACT($AE$1:$AE$1299,"A"),$AE$1:$AE$1299,0)


example of an EXACT, that was able to get to work in combo with other:
=SUMPRODUCT(--(EXACT(LEFT($AE$112:$AE$1300,1),"A")))

hi, yes to all.. just the letters: A, a, B, b, C, c, D, d
just relative position: yes if getting the row number, is what that means.

problems that arise, I believe are from actually doing work but some
functions only do half the job. e.g.: would think programming would have
allowed easy exclusion of top rows where having absolute cell references that
control that column have:
A, a, B.. as part of the settings; but then, the formula will not work.
I would have allowed programming to use: EXACT, ROW<(), then "" nothing...

- either that or it's just some trick I haven't quite worked out.
- or uniqueness of problems, haven't been thought of by others, ... thanks


"Biff" wrote:

What kind of data do you have in your range?

Do the cells just contain the single letter "A" or do they contain strings
like "Address" or "adult" ?

So, if you're using MATCH then you just want the relative position of "A"
within the array AE1:AE1299 ?

Your posts always confuse me!

Biff

"nastech" wrote in message
...
060724 Match, Exact? NA error
hi, trying to make an "exact" match for the case, of a character, e.g.:
"A"

where the following works, am trying to isolate capital "A"'s.
=MATCH("A",$AE$1:$AE$1299,0)

the following receives a NA error. Is there a correct way to represent?
am open to inserting IF statements, EXACT, or ROW<=().. etc.
=MATCH(EXACT($AE$1:$AE$1299,"A"),$AE$1:$AE$1299,0)


example of an EXACT, that was able to get to work in combo with other:
=SUMPRODUCT(--(EXACT(LEFT($AE$112:$AE$1300,1),"A")))

OK.....

If there's only one instance of "A":

=SUMPRODUCT(--(EXACT(AE1:AE1299,"A")),ROW(AE1:AE1299))

If there might be more than one instance of "A":

This will find the first instance:

Entered as an array using the key combination of CTRL,SHIFT,ENTER:

=INDEX(ROW(AE1:AE1299),MATCH(TRUE,EXACT(AE1:AE1299 ,"A"),0))

Biff

"nastech" wrote in message
...
hi, yes to all.. just the letters: A, a, B, b, C, c, D, d
just relative position: yes if getting the row number, is what that
means.

problems that arise, I believe are from actually doing work but some
functions only do half the job. e.g.: would think programming would have
allowed easy exclusion of top rows where having absolute cell references
that
control that column have:
A, a, B.. as part of the settings; but then, the formula will not work.
I would have allowed programming to use: EXACT, ROW<(), then ""
nothing...

- either that or it's just some trick I haven't quite worked out.
- or uniqueness of problems, haven't been thought of by others, ...
thanks


"Biff" wrote:

What kind of data do you have in your range?

Do the cells just contain the single letter "A" or do they contain
strings
like "Address" or "adult" ?

So, if you're using MATCH then you just want the relative position of "A"
within the array AE1:AE1299 ?

Your posts always confuse me!

Biff

"nastech" wrote in message
...
060724 Match, Exact? NA error
hi, trying to make an "exact" match for the case, of a character, e.g.:
"A"

where the following works, am trying to isolate capital "A"'s.
=MATCH("A",$AE$1:$AE$1299,0)

the following receives a NA error. Is there a correct way to
represent?
am open to inserting IF statements, EXACT, or ROW<=().. etc.
=MATCH(EXACT($AE$1:$AE$1299,"A"),$AE$1:$AE$1299,0)


example of an EXACT, that was able to get to work in combo with other:
=SUMPRODUCT(--(EXACT(LEFT($AE$112:$AE$1300,1),"A")))

Hi, thanks.. sorry, many instances of each letter, so the 2nd example worked;

but wondering if way to exclude top rows, in case have cells with same
letter "A" as settings? trying to insert ROW5...

"Biff" wrote:

OK.....

If there's only one instance of "A":

=SUMPRODUCT(--(EXACT(AE1:AE1299,"A")),ROW(AE1:AE1299))

If there might be more than one instance of "A":

This will find the first instance:

Entered as an array using the key combination of CTRL,SHIFT,ENTER:

=INDEX(ROW(AE1:AE1299),MATCH(TRUE,EXACT(AE1:AE1299 ,"A"),0))

Biff

"nastech" wrote in message
...
hi, yes to all.. just the letters: A, a, B, b, C, c, D, d
just relative position: yes if getting the row number, is what that
means.

problems that arise, I believe are from actually doing work but some
functions only do half the job. e.g.: would think programming would have
allowed easy exclusion of top rows where having absolute cell references
that
control that column have:
A, a, B.. as part of the settings; but then, the formula will not work.
I would have allowed programming to use: EXACT, ROW<(), then ""
nothing...

- either that or it's just some trick I haven't quite worked out.
- or uniqueness of problems, haven't been thought of by others, ...
thanks


"Biff" wrote:

What kind of data do you have in your range?

Do the cells just contain the single letter "A" or do they contain
strings
like "Address" or "adult" ?

So, if you're using MATCH then you just want the relative position of "A"
within the array AE1:AE1299 ?

Your posts always confuse me!

Biff

"nastech" wrote in message
...
060724 Match, Exact? NA error
hi, trying to make an "exact" match for the case, of a character, e.g.:
"A"

where the following works, am trying to isolate capital "A"'s.
=MATCH("A",$AE$1:$AE$1299,0)

the following receives a NA error. Is there a correct way to
represent?
am open to inserting IF statements, EXACT, or ROW<=().. etc.
=MATCH(EXACT($AE$1:$AE$1299,"A"),$AE$1:$AE$1299,0)


example of an EXACT, that was able to get to work in combo with other:
=SUMPRODUCT(--(EXACT(LEFT($AE$112:$AE$1300,1),"A")))

Just change the range to start at AE6:

For the RELATIVE row location:

=INDEX(ROW(AE6:AE1299)-ROW(AE6)+1),MATCH(TRUE,EXACT(AE6:AE1299,"A"),0))

For the ACTUAL row location:

=INDEX(ROW(AE6:AE1299),MATCH(TRUE,EXACT(AE6:AE1299 ,"A"),0))

Biff

"nastech" wrote in message
...
Hi, thanks.. sorry, many instances of each letter, so the 2nd example
worked;

but wondering if way to exclude top rows, in case have cells with same
letter "A" as settings? trying to insert ROW5...

"Biff" wrote:

OK.....

If there's only one instance of "A":

=SUMPRODUCT(--(EXACT(AE1:AE1299,"A")),ROW(AE1:AE1299))

If there might be more than one instance of "A":

This will find the first instance:

Entered as an array using the key combination of CTRL,SHIFT,ENTER:

=INDEX(ROW(AE1:AE1299),MATCH(TRUE,EXACT(AE1:AE1299 ,"A"),0))

Biff

"nastech" wrote in message
...
hi, yes to all.. just the letters: A, a, B, b, C, c, D, d
just relative position: yes if getting the row number, is what that
means.

problems that arise, I believe are from actually doing work but some
functions only do half the job. e.g.: would think programming would
have
allowed easy exclusion of top rows where having absolute cell
references
that
control that column have:
A, a, B.. as part of the settings; but then, the formula will not
work.
I would have allowed programming to use: EXACT, ROW<(), then ""
nothing...

- either that or it's just some trick I haven't quite worked out.
- or uniqueness of problems, haven't been thought of by others, ...
thanks


"Biff" wrote:

What kind of data do you have in your range?

Do the cells just contain the single letter "A" or do they contain
strings
like "Address" or "adult" ?

So, if you're using MATCH then you just want the relative position of
"A"
within the array AE1:AE1299 ?

Your posts always confuse me!

Biff

"nastech" wrote in message
...
060724 Match, Exact? NA error
hi, trying to make an "exact" match for the case, of a character,
e.g.:
"A"

where the following works, am trying to isolate capital "A"'s.
=MATCH("A",$AE$1:$AE$1299,0)

the following receives a NA error. Is there a correct way to
represent?
am open to inserting IF statements, EXACT, or ROW<=().. etc.
=MATCH(EXACT($AE$1:$AE$1299,"A"),$AE$1:$AE$1299,0)


example of an EXACT, that was able to get to work in combo with
other:
=SUMPRODUCT(--(EXACT(LEFT($AE$112:$AE$1300,1),"A")))

Thankyou very much.. will be able to accomplish daily tasks (dozens -
hundreds of them) much faster once finish getting set up. the formula's are
part of a hyper-link I thought up with some help, to find values quickly.

Hyperlinks to exact locations in a document (link that move as lines move):
=HYPERLINK(IF(ROW($A$650)<=ROW($A65),
"#"&CELL("address",OFFSET($A$650,-1,1)),"#"&CELL("address",OFFSET($A$650,$V$1,1)))," A")

where $V$1 is a set number of lines to offset, with equation:
=48-CELL("row",$A$17) to automate offset value

where ROW($A65), must be the row formula link is currently in.
NOTE: to make work "IN-DOCUMENT" lines, where link is in SAME TITLE LINE/
After, as destination put 2nd half of formula 1st, and reverse <=ROW to =
(SAVES large amount of time, when working on large sheets).

----- for your help, item:

The only thing that will remain is to include in column where muliple A B C
D's repeat, are to negate them as I pass thru them with a date function.

maybe something like: IF((NOW()+1)(AZ9+AZ$5)),"",formula
no problem.


=HYPERLINK(IF(ISNA(INDEX(ROW($AE$1:$AE$1303),MATCH (TRUE,EXACT($AE$1:$AE$1303,"A"),0))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",$V14),"$",""),ROW(),"")&INDE X(ROW($AE$1:$AE$1303),MATCH(TRUE,EXACT($AE$1:$AE$1 303,"A"),0))),$V$1,0))),"A")

will find the 1st capital "A" in that column. have to add exclusion you
qouted for ROWS.


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

"Biff" wrote:

Just change the range to start at AE6:

For the RELATIVE row location:

=INDEX(ROW(AE6:AE1299)-ROW(AE6)+1),MATCH(TRUE,EXACT(AE6:AE1299,"A"),0))

For the ACTUAL row location:

=INDEX(ROW(AE6:AE1299),MATCH(TRUE,EXACT(AE6:AE1299 ,"A"),0))

Biff

"nastech" wrote in message
...
Hi, thanks.. sorry, many instances of each letter, so the 2nd example
worked;

but wondering if way to exclude top rows, in case have cells with same
letter "A" as settings? trying to insert ROW5...

"Biff" wrote:

OK.....

If there's only one instance of "A":

=SUMPRODUCT(--(EXACT(AE1:AE1299,"A")),ROW(AE1:AE1299))

If there might be more than one instance of "A":

This will find the first instance:

Entered as an array using the key combination of CTRL,SHIFT,ENTER:

=INDEX(ROW(AE1:AE1299),MATCH(TRUE,EXACT(AE1:AE1299 ,"A"),0))

Biff

"nastech" wrote in message
...
hi, yes to all.. just the letters: A, a, B, b, C, c, D, d
just relative position: yes if getting the row number, is what that
means.

problems that arise, I believe are from actually doing work but some
functions only do half the job. e.g.: would think programming would
have
allowed easy exclusion of top rows where having absolute cell
references
that
control that column have:
A, a, B.. as part of the settings; but then, the formula will not
work.
I would have allowed programming to use: EXACT, ROW<(), then ""
nothing...

- either that or it's just some trick I haven't quite worked out.
- or uniqueness of problems, haven't been thought of by others, ...
thanks


"Biff" wrote:

What kind of data do you have in your range?

Do the cells just contain the single letter "A" or do they contain
strings
like "Address" or "adult" ?

So, if you're using MATCH then you just want the relative position of
"A"
within the array AE1:AE1299 ?

Your posts always confuse me!

Biff

"nastech" wrote in message
...
060724 Match, Exact? NA error
hi, trying to make an "exact" match for the case, of a character,
e.g.:
"A"

where the following works, am trying to isolate capital "A"'s.
=MATCH("A",$AE$1:$AE$1299,0)

the following receives a NA error. Is there a correct way to
represent?
am open to inserting IF statements, EXACT, or ROW<=().. etc.
=MATCH(EXACT($AE$1:$AE$1299,"A"),$AE$1:$AE$1299,0)


example of an EXACT, that was able to get to work in combo with
other:
=SUMPRODUCT(--(EXACT(LEFT($AE$112:$AE$1300,1),"A")))

As I did not think we would get this far, did not want to include too much,
but searching for an error in 1 column (Similar formula) not working.

is there something I am doing wrong with the IF(ISNA ... ,10000?
not getting the rows to skip the header with your suggestion, in this
circumstannce.
Thanks much

cannot quite figure out the following. may be has some to do with negating
ROWS you showed, but think something else the problem as well.

Finding error in column, (hyperlink) does not quite work:

=HYPERLINK(IF(ISNA(MIN(IF(ISERROR($AE$1:$AE$1303), ROW($AE$56:$AE$1303)-ROW(AE56)+1,10000))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",AE15),"$",""),ROW(),"")&MIN( IF(ISERROR($AE$56:$AE$1303),ROW($AE$56:$AE$1303)," "))),$V$1,0))),"E")

although have the following at the top, it is not the whole problem.
=SUMPRODUCT(--(EXACT(LEFT($AE$115:$AE$1303,1),{"A","B","C"})))

- comes up with same error, if error 1,000 lines below has an error,
iterferes with the hyperlink search formula.
- the next formula works before line 115 when there is an error, but the
"NOT" is not right.
- if no error, link goes to some erroneous position.

The following works before line 115.

=HYPERLINK(IF(ISNA(MIN(IF(ISERROR($AE$1:$AE$1303), ROW($AE$1:$AE$1303),10000))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",AE15),"$",""),ROW(),"")&MIN( IF(ISERROR($AE$1:$AE$1303),ROW($AE$1:$AE$1303),"") )),$V$1,0))),"E")

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

"Biff" wrote:

Just change the range to start at AE6:

For the RELATIVE row location:

=INDEX(ROW(AE6:AE1299)-ROW(AE6)+1),MATCH(TRUE,EXACT(AE6:AE1299,"A"),0))

For the ACTUAL row location:

=INDEX(ROW(AE6:AE1299),MATCH(TRUE,EXACT(AE6:AE1299 ,"A"),0))

Biff

"nastech" wrote in message
...
Hi, thanks.. sorry, many instances of each letter, so the 2nd example
worked;

but wondering if way to exclude top rows, in case have cells with same
letter "A" as settings? trying to insert ROW5...

"Biff" wrote:

OK.....

If there's only one instance of "A":

=SUMPRODUCT(--(EXACT(AE1:AE1299,"A")),ROW(AE1:AE1299))

If there might be more than one instance of "A":

This will find the first instance:

Entered as an array using the key combination of CTRL,SHIFT,ENTER:

=INDEX(ROW(AE1:AE1299),MATCH(TRUE,EXACT(AE1:AE1299 ,"A"),0))

Biff

"nastech" wrote in message
...
hi, yes to all.. just the letters: A, a, B, b, C, c, D, d
just relative position: yes if getting the row number, is what that
means.

problems that arise, I believe are from actually doing work but some
functions only do half the job. e.g.: would think programming would
have
allowed easy exclusion of top rows where having absolute cell
references
that
control that column have:
A, a, B.. as part of the settings; but then, the formula will not
work.
I would have allowed programming to use: EXACT, ROW<(), then ""
nothing...

- either that or it's just some trick I haven't quite worked out.
- or uniqueness of problems, haven't been thought of by others, ...
thanks


"Biff" wrote:

What kind of data do you have in your range?

Do the cells just contain the single letter "A" or do they contain
strings
like "Address" or "adult" ?

So, if you're using MATCH then you just want the relative position of
"A"
within the array AE1:AE1299 ?

Your posts always confuse me!

Biff

"nastech" wrote in message
...
060724 Match, Exact? NA error
hi, trying to make an "exact" match for the case, of a character,
e.g.:
"A"

where the following works, am trying to isolate capital "A"'s.
=MATCH("A",$AE$1:$AE$1299,0)

the following receives a NA error. Is there a correct way to
represent?
am open to inserting IF statements, EXACT, or ROW<=().. etc.
=MATCH(EXACT($AE$1:$AE$1299,"A"),$AE$1:$AE$1299,0)


example of an EXACT, that was able to get to work in combo with
other:
=SUMPRODUCT(--(EXACT(LEFT($AE$112:$AE$1300,1),"A")))

You've lost me!

If you have errors in a range and that screws things up, fix the errors!

Biff

"nastech" wrote in message
...
As I did not think we would get this far, did not want to include too
much,
but searching for an error in 1 column (Similar formula) not working.

is there something I am doing wrong with the IF(ISNA ... ,10000?
not getting the rows to skip the header with your suggestion, in this
circumstannce.
Thanks much

cannot quite figure out the following. may be has some to do with
negating
ROWS you showed, but think something else the problem as well.

Finding error in column, (hyperlink) does not quite work:

=HYPERLINK(IF(ISNA(MIN(IF(ISERROR($AE$1:$AE$1303), ROW($AE$56:$AE$1303)-ROW(AE56)+1,10000))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",AE15),"$",""),ROW(),"")&MIN( IF(ISERROR($AE$56:$AE$1303),ROW($AE$56:$AE$1303)," "))),$V$1,0))),"E")

although have the following at the top, it is not the whole problem.
=SUMPRODUCT(--(EXACT(LEFT($AE$115:$AE$1303,1),{"A","B","C"})))

- comes up with same error, if error 1,000 lines below has an error,
iterferes with the hyperlink search formula.
- the next formula works before line 115 when there is an error, but the
"NOT" is not right.
- if no error, link goes to some erroneous position.

The following works before line 115.

=HYPERLINK(IF(ISNA(MIN(IF(ISERROR($AE$1:$AE$1303), ROW($AE$1:$AE$1303),10000))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",AE15),"$",""),ROW(),"")&MIN( IF(ISERROR($AE$1:$AE$1303),ROW($AE$1:$AE$1303),"") )),$V$1,0))),"E")

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

"Biff" wrote:

Just change the range to start at AE6:

For the RELATIVE row location:

=INDEX(ROW(AE6:AE1299)-ROW(AE6)+1),MATCH(TRUE,EXACT(AE6:AE1299,"A"),0))

For the ACTUAL row location:

=INDEX(ROW(AE6:AE1299),MATCH(TRUE,EXACT(AE6:AE1299 ,"A"),0))

Biff

"nastech" wrote in message
...
Hi, thanks.. sorry, many instances of each letter, so the 2nd example
worked;

but wondering if way to exclude top rows, in case have cells with same
letter "A" as settings? trying to insert ROW5...

"Biff" wrote:

OK.....

If there's only one instance of "A":

=SUMPRODUCT(--(EXACT(AE1:AE1299,"A")),ROW(AE1:AE1299))

If there might be more than one instance of "A":

This will find the first instance:

Entered as an array using the key combination of CTRL,SHIFT,ENTER:

=INDEX(ROW(AE1:AE1299),MATCH(TRUE,EXACT(AE1:AE1299 ,"A"),0))

Biff

"nastech" wrote in message
...
hi, yes to all.. just the letters: A, a, B, b, C, c, D, d
just relative position: yes if getting the row number, is what that
means.

problems that arise, I believe are from actually doing work but some
functions only do half the job. e.g.: would think programming
would
have
allowed easy exclusion of top rows where having absolute cell
references
that
control that column have:
A, a, B.. as part of the settings; but then, the formula will not
work.
I would have allowed programming to use: EXACT, ROW<(), then ""
nothing...

- either that or it's just some trick I haven't quite worked out.
- or uniqueness of problems, haven't been thought of by others, ...
thanks


"Biff" wrote:

What kind of data do you have in your range?

Do the cells just contain the single letter "A" or do they contain
strings
like "Address" or "adult" ?

So, if you're using MATCH then you just want the relative position
of
"A"
within the array AE1:AE1299 ?

Your posts always confuse me!

Biff

"nastech" wrote in message
...
060724 Match, Exact? NA error
hi, trying to make an "exact" match for the case, of a character,
e.g.:
"A"

where the following works, am trying to isolate capital "A"'s.
=MATCH("A",$AE$1:$AE$1299,0)

the following receives a NA error. Is there a correct way to
represent?
am open to inserting IF statements, EXACT, or ROW<=().. etc.
=MATCH(EXACT($AE$1:$AE$1299,"A"),$AE$1:$AE$1299,0)


example of an EXACT, that was able to get to work in combo with
other:
=SUMPRODUCT(--(EXACT(LEFT($AE$112:$AE$1300,1),"A")))

hi, sorry, giving too much info, rather not be asked why, guesse just the
formula matters.. didn't want to confuse anyone, but have reasons that don't
matter to tell why. you probably don't want to know the work / (amount of)
that am doing, the amount of changes that take place, "not" errors? get
induced.. divide by zero monsters you didn't see comming? anyways, just
looking for a tool.
the problem is what was trying to find.
as said, it is in some line, 1000 lines or so, down somewhere.
you might be asking for too much info. more?
sometimes have intermittent induced errors,





"Biff" wrote:

You've lost me!

If you have errors in a range and that screws things up, fix the errors!

Biff

"nastech" wrote in message
...
As I did not think we would get this far, did not want to include too
much,
but searching for an error in 1 column (Similar formula) not working.

is there something I am doing wrong with the IF(ISNA ... ,10000?
not getting the rows to skip the header with your suggestion, in this
circumstannce.
Thanks much

cannot quite figure out the following. may be has some to do with
negating
ROWS you showed, but think something else the problem as well.

Finding error in column, (hyperlink) does not quite work:

=HYPERLINK(IF(ISNA(MIN(IF(ISERROR($AE$1:$AE$1303), ROW($AE$56:$AE$1303)-ROW(AE56)+1,10000))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",AE15),"$",""),ROW(),"")&MIN( IF(ISERROR($AE$56:$AE$1303),ROW($AE$56:$AE$1303)," "))),$V$1,0))),"E")

although have the following at the top, it is not the whole problem.
=SUMPRODUCT(--(EXACT(LEFT($AE$115:$AE$1303,1),{"A","B","C"})))

- comes up with same error, if error 1,000 lines below has an error,
iterferes with the hyperlink search formula.
- the next formula works before line 115 when there is an error, but the
"NOT" is not right.
- if no error, link goes to some erroneous position.

The following works before line 115.

=HYPERLINK(IF(ISNA(MIN(IF(ISERROR($AE$1:$AE$1303), ROW($AE$1:$AE$1303),10000))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",AE15),"$",""),ROW(),"")&MIN( IF(ISERROR($AE$1:$AE$1303),ROW($AE$1:$AE$1303),"") )),$V$1,0))),"E")

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

"Biff" wrote:

Just change the range to start at AE6:

For the RELATIVE row location:

=INDEX(ROW(AE6:AE1299)-ROW(AE6)+1),MATCH(TRUE,EXACT(AE6:AE1299,"A"),0))

For the ACTUAL row location:

=INDEX(ROW(AE6:AE1299),MATCH(TRUE,EXACT(AE6:AE1299 ,"A"),0))

Biff

"nastech" wrote in message
...
Hi, thanks.. sorry, many instances of each letter, so the 2nd example
worked;

but wondering if way to exclude top rows, in case have cells with same
letter "A" as settings? trying to insert ROW5...

"Biff" wrote:

OK.....

If there's only one instance of "A":

=SUMPRODUCT(--(EXACT(AE1:AE1299,"A")),ROW(AE1:AE1299))

If there might be more than one instance of "A":

This will find the first instance:

Entered as an array using the key combination of CTRL,SHIFT,ENTER:

=INDEX(ROW(AE1:AE1299),MATCH(TRUE,EXACT(AE1:AE1299 ,"A"),0))

Biff

"nastech" wrote in message
...
hi, yes to all.. just the letters: A, a, B, b, C, c, D, d
just relative position: yes if getting the row number, is what that
means.

problems that arise, I believe are from actually doing work but some
functions only do half the job. e.g.: would think programming
would
have
allowed easy exclusion of top rows where having absolute cell
references
that
control that column have:
A, a, B.. as part of the settings; but then, the formula will not
work.
I would have allowed programming to use: EXACT, ROW<(), then ""
nothing...

- either that or it's just some trick I haven't quite worked out.
- or uniqueness of problems, haven't been thought of by others, ...
thanks


"Biff" wrote:

What kind of data do you have in your range?

Do the cells just contain the single letter "A" or do they contain
strings
like "Address" or "adult" ?

So, if you're using MATCH then you just want the relative position
of
"A"
within the array AE1:AE1299 ?

Your posts always confuse me!

Biff

"nastech" wrote in message
...
060724 Match, Exact? NA error
hi, trying to make an "exact" match for the case, of a character,
e.g.:
"A"

where the following works, am trying to isolate capital "A"'s.
=MATCH("A",$AE$1:$AE$1299,0)

the following receives a NA error. Is there a correct way to
represent?
am open to inserting IF statements, EXACT, or ROW<=().. etc.
=MATCH(EXACT($AE$1:$AE$1299,"A"),$AE$1:$AE$1299,0)


example of an EXACT, that was able to get to work in combo with
other:
=SUMPRODUCT(--(EXACT(LEFT($AE$112:$AE$1300,1),"A")))

um.. retract, up 24 hours... items searched for in errors keep popping up
(different ways), just trying to quickly find the lines to quickly fix /
delete. am having some trouble adding idea into formula's: -ROW(AE6)+1),
just attention to detail thing with different variations. will work on it,
thanks.

"Biff" wrote:

You've lost me!

If you have errors in a range and that screws things up, fix the errors!

Biff

"nastech" wrote in message
...
As I did not think we would get this far, did not want to include too
much,
but searching for an error in 1 column (Similar formula) not working.

is there something I am doing wrong with the IF(ISNA ... ,10000?
not getting the rows to skip the header with your suggestion, in this
circumstannce.
Thanks much

cannot quite figure out the following. may be has some to do with
negating
ROWS you showed, but think something else the problem as well.

Finding error in column, (hyperlink) does not quite work:

=HYPERLINK(IF(ISNA(MIN(IF(ISERROR($AE$1:$AE$1303), ROW($AE$56:$AE$1303)-ROW(AE56)+1,10000))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",AE15),"$",""),ROW(),"")&MIN( IF(ISERROR($AE$56:$AE$1303),ROW($AE$56:$AE$1303)," "))),$V$1,0))),"E")

although have the following at the top, it is not the whole problem.
=SUMPRODUCT(--(EXACT(LEFT($AE$115:$AE$1303,1),{"A","B","C"})))

- comes up with same error, if error 1,000 lines below has an error,
iterferes with the hyperlink search formula.
- the next formula works before line 115 when there is an error, but the
"NOT" is not right.
- if no error, link goes to some erroneous position.

The following works before line 115.

=HYPERLINK(IF(ISNA(MIN(IF(ISERROR($AE$1:$AE$1303), ROW($AE$1:$AE$1303),10000))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",AE15),"$",""),ROW(),"")&MIN( IF(ISERROR($AE$1:$AE$1303),ROW($AE$1:$AE$1303),"") )),$V$1,0))),"E")

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

"Biff" wrote:

Just change the range to start at AE6:

For the RELATIVE row location:

=INDEX(ROW(AE6:AE1299)-ROW(AE6)+1),MATCH(TRUE,EXACT(AE6:AE1299,"A"),0))

For the ACTUAL row location:

=INDEX(ROW(AE6:AE1299),MATCH(TRUE,EXACT(AE6:AE1299 ,"A"),0))

Biff

"nastech" wrote in message
...
Hi, thanks.. sorry, many instances of each letter, so the 2nd example
worked;

but wondering if way to exclude top rows, in case have cells with same
letter "A" as settings? trying to insert ROW5...

"Biff" wrote:

OK.....

If there's only one instance of "A":

=SUMPRODUCT(--(EXACT(AE1:AE1299,"A")),ROW(AE1:AE1299))

If there might be more than one instance of "A":

This will find the first instance:

Entered as an array using the key combination of CTRL,SHIFT,ENTER:

=INDEX(ROW(AE1:AE1299),MATCH(TRUE,EXACT(AE1:AE1299 ,"A"),0))

Biff

"nastech" wrote in message
...
hi, yes to all.. just the letters: A, a, B, b, C, c, D, d
just relative position: yes if getting the row number, is what that
means.

problems that arise, I believe are from actually doing work but some
functions only do half the job. e.g.: would think programming
would
have
allowed easy exclusion of top rows where having absolute cell
references
that
control that column have:
A, a, B.. as part of the settings; but then, the formula will not
work.
I would have allowed programming to use: EXACT, ROW<(), then ""
nothing...

- either that or it's just some trick I haven't quite worked out.
- or uniqueness of problems, haven't been thought of by others, ...
thanks


"Biff" wrote:

What kind of data do you have in your range?

Do the cells just contain the single letter "A" or do they contain
strings
like "Address" or "adult" ?

So, if you're using MATCH then you just want the relative position
of
"A"
within the array AE1:AE1299 ?

Your posts always confuse me!

Biff

"nastech" wrote in message
...
060724 Match, Exact? NA error
hi, trying to make an "exact" match for the case, of a character,
e.g.:
"A"

where the following works, am trying to isolate capital "A"'s.
=MATCH("A",$AE$1:$AE$1299,0)

the following receives a NA error. Is there a correct way to
represent?
am open to inserting IF statements, EXACT, or ROW<=().. etc.
=MATCH(EXACT($AE$1:$AE$1299,"A"),$AE$1:$AE$1299,0)


example of an EXACT, that was able to get to work in combo with
other:
=SUMPRODUCT(--(EXACT(LEFT($AE$112:$AE$1300,1),"A")))

hi, thanks for your help before, found pieces for what was doing.. not sure
if interested buy just in case. reasons were not for errors so much as tests
for negative conditions that pop up often; needed a way to quickly navigate.
here is a copy of what I found... thanks for the help.


ANSWER TO: Find Error result in column, where calculated (pick a start
point & exclude a range), Hyperlink to it (with row number as friendly name
in hyperlink)

=HYPERLINK(IF(AND(
ISNA(INDEX(ROW($AX$173:$AX$540)-ROW(AX173)+1,MATCH(TRUE,EXACT($AX$173:$AX$540,"X") ,0))),
ISNA(INDEX(ROW($AX$567:$AX$1168)-ROW(AX567)+1,MATCH(TRUE,EXACT($AX$567:$AX$1168,"X" ),0)))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",$W14),"$",""),ROW(),"")&INDE X(ROW($AX$173:$AX$1168),MATCH(TRUE,EXACT($AX$173:$ AX$1168,"X"),0))),$W$1,0))),
IF($BX$150,IF(AND(
ISNA(INDEX(ROW($AX$173:$AX$540)-ROW(AX173)+1,MATCH(TRUE,EXACT($AX$173:$AX$540,"X") ,0))),
ISNA(INDEX(ROW($AX$567:$AX$1168)-ROW(AX567)+1,MATCH(TRUE,EXACT($AX$567:$AX$1168,"X" ),0)))),"",
INDEX(ROW($AX$173:$AX$1168),MATCH(TRUE,EXACT($AX$1 73:$AX$1168,"X"),0))),"-"))

note: $W14 is the row that that formula resides in, W is column you want
cursor to end up in.

Quantity of Errors:
=IF($G$7="x",SUMPRODUCT(--(LEFT($AX$173:$AX$540,1)="x"))+SUMPRODUCT(--(LEFT($AX$567:$AX$1168,1)="x")),
SUMPRODUCT(--(LEFT($AX$173:$AX$540,1)="x"),--($G$173:$G$540<"x")))+SUMPRODUCT(--(LEFT($AX$567:$AX$1168,1)="x"),--($G$567:$G$1168<"x"))

SAMPLE: Test for errors:
=IF(AND(S9<".",OR($G$7="x",G9<"x"),OR(CP9={"",0} )),"X",

OTHER:
=HYPERLINK("#"&CELL("address",OFFSET($A$69,-1,1)),"top")

IN-DOCUMENT Hyperlink: GETS YOU BACK TO EXACT SAME SPOT, EVERYTIME.
=HYPERLINK(IF(ROW($A$607)<(ROW($A50)-($W$1/2)),"#"&CELL("address",OFFSET($A$607,-1,1)),"#"&CELL("address",OFFSET($A$607,$W$1,1)))," A")

($A50 is row formula resides in)
Utility: $W$1 method for screen size / from freezepane (any # minus size
of freezepane = rows showing)
=48-CELL("row",$A$17)



XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

"Biff" wrote:

Just change the range to start at AE6:

For the RELATIVE row location:

=INDEX(ROW(AE6:AE1299)-ROW(AE6)+1),MATCH(TRUE,EXACT(AE6:AE1299,"A"),0))

For the ACTUAL row location:

=INDEX(ROW(AE6:AE1299),MATCH(TRUE,EXACT(AE6:AE1299 ,"A"),0))

Biff

"nastech" wrote in message
...
Hi, thanks.. sorry, many instances of each letter, so the 2nd example
worked;

but wondering if way to exclude top rows, in case have cells with same
letter "A" as settings? trying to insert ROW5...

"Biff" wrote:

OK.....

If there's only one instance of "A":

=SUMPRODUCT(--(EXACT(AE1:AE1299,"A")),ROW(AE1:AE1299))

If there might be more than one instance of "A":

This will find the first instance:

Entered as an array using the key combination of CTRL,SHIFT,ENTER:

=INDEX(ROW(AE1:AE1299),MATCH(TRUE,EXACT(AE1:AE1299 ,"A"),0))

Biff

"nastech" wrote in message
...
hi, yes to all.. just the letters: A, a, B, b, C, c, D, d
just relative position: yes if getting the row number, is what that
means.

problems that arise, I believe are from actually doing work but some
functions only do half the job. e.g.: would think programming would
have
allowed easy exclusion of top rows where having absolute cell
references
that
control that column have:
A, a, B.. as part of the settings; but then, the formula will not
work.
I would have allowed programming to use: EXACT, ROW<(), then ""
nothing...

- either that or it's just some trick I haven't quite worked out.
- or uniqueness of problems, haven't been thought of by others, ...
thanks


"Biff" wrote:

What kind of data do you have in your range?

Do the cells just contain the single letter "A" or do they contain
strings
like "Address" or "adult" ?

So, if you're using MATCH then you just want the relative position of
"A"
within the array AE1:AE1299 ?

Your posts always confuse me!

Biff

"nastech" wrote in message
...
060724 Match, Exact? NA error
hi, trying to make an "exact" match for the case, of a character,
e.g.:
"A"

where the following works, am trying to isolate capital "A"'s.
=MATCH("A",$AE$1:$AE$1299,0)

the following receives a NA error. Is there a correct way to
represent?
am open to inserting IF statements, EXACT, or ROW<=().. etc.
=MATCH(EXACT($AE$1:$AE$1299,"A"),$AE$1:$AE$1299,0)


example of an EXACT, that was able to get to work in combo with
other:
=SUMPRODUCT(--(EXACT(LEFT($AE$112:$AE$1300,1),"A")))