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