nastech wrote:
060716 MATCH function, exclusion question

hi, trying to use MATCH to find the first instance of items, but
want to exlude the top of document where there may be one or more of
the "instances- item erors"

the following does not work for receiving a #VALUE error.
e.g.:
AND(ROW()=$B$56,MATCH("dd",$D$1:$D$1303,0))


full example in use: is for hyperlink to first instance:

=HYPERLINK(IF(ISNA(MATCH("X",$D$1:$D$1303,10000)), "",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",W12),"$",""),ROW(),"")&
AND(ROW()=$B$56,MATCH("dd",$D$1:$D$1303,0))),$W$1 ,0))),"dd")


notes:
- remove AND(ROW()=$B$56, and following: ")"
- to work: W12, is column where want link to end up, 12 is row this
formula resides. thanks

$B$56 isn't a row!
ROW()=56
is how you should write something like that

thankyou. I guesse I had been swapping values around enough, that I did not
realize that. not sure but think if I was referring to a cell with a value,
say: 56
then the following would be correct for that?: AND(ROW()=$BC$15,

either way, what I was trying to do is not working, may have to ask another
way.

is the match function a problem, since it looks for the first value,
desired, in a range?
if is a problem, do not think was able to pick past the first row for the
match function, for result to give a correct row number "away" from start of
range.. (make sense?) maybe match is not what should be using. (trying to
find first error "after" header row where same error appears, means would
never get past the header row). thanks

"Paul Lautman" wrote:

nastech wrote:
060716 MATCH function, exclusion question

hi, trying to use MATCH to find the first instance of items, but
want to exlude the top of document where there may be one or more of
the "instances- item erors"

the following does not work for receiving a #VALUE error.
e.g.:
AND(ROW()=$B$56,MATCH("dd",$D$1:$D$1303,0))


full example in use: is for hyperlink to first instance:

=HYPERLINK(IF(ISNA(MATCH("X",$D$1:$D$1303,10000)), "",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",W12),"$",""),ROW(),"")&
AND(ROW()=$B$56,MATCH("dd",$D$1:$D$1303,0))),$W$1 ,0))),"dd")


notes:
- remove AND(ROW()=$B$56, and following: ")"
- to work: W12, is column where want link to end up, 12 is row this
formula resides. thanks

$B$56 isn't a row!
ROW()=56
is how you should write something like that

nastech wrote:
thankyou. I guesse I had been swapping values around enough, that I
did not realize that. not sure but think if I was referring to a
cell with a value, say: 56
then the following would be correct for that?: AND(ROW()=$BC$15,

either way, what I was trying to do is not working, may have to ask
another way.

is the match function a problem, since it looks for the first value,
desired, in a range?
if is a problem, do not think was able to pick past the first row for
the match function, for result to give a correct row number "away"
from start of range.. (make sense?) maybe match is not what should
be using. (trying to find first error "after" header row where same
error appears, means would never get past the header row). thanks

Please don't top post. It makes following the thread very difficult.

I realised after I had posted that of course the cell would contain the
value that you were interested in. A funny 5 minutes on my part I'm afraid!

I don't understand what you mean when you say: "the following does not work
for receiving a #VALUE error"

Can you try explaining this a different way?

I also don' tknow what the "instances-item erors" would be?


"Paul Lautman" wrote:

nastech wrote:
060716 MATCH function, exclusion question

hi, trying to use MATCH to find the first instance of items, but
want to exlude the top of document where there may be one or more of
the "instances- item erors"

the following does not work for receiving a #VALUE error.
e.g.:
AND(ROW()=$B$56,MATCH("dd",$D$1:$D$1303,0))


full example in use: is for hyperlink to first instance:

=HYPERLINK(IF(ISNA(MATCH("X",$D$1:$D$1303,10000)), "",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",W12),"$",""),ROW(),"")&
AND(ROW()=$B$56,MATCH("dd",$D$1:$D$1303,0))),$W$1 ,0))),"dd")


notes:
- remove AND(ROW()=$B$56, and following: ")"
- to work: W12, is column where want link to end up, 12 is row
this formula resides. thanks

$B$56 isn't a row!
ROW()=56
is how you should write something like that

"Paul Lautman" wrote:

nastech wrote:
thankyou. I guesse I had been swapping values around enough, that I
did not realize that. not sure but think if I was referring to a
cell with a value, say: 56
then the following would be correct for that?: AND(ROW()=$BC$15,

either way, what I was trying to do is not working, may have to ask
another way.

is the match function a problem, since it looks for the first value,
desired, in a range?
if is a problem, do not think was able to pick past the first row for
the match function, for result to give a correct row number "away"
from start of range.. (make sense?) maybe match is not what should
be using. (trying to find first error "after" header row where same
error appears, means would never get past the header row). thanks

Please don't top post. It makes following the thread very difficult.

I realised after I had posted that of course the cell would contain the
value that you were interested in. A funny 5 minutes on my part I'm afraid!

I don't understand what you mean when you say: "the following does not work
for receiving a #VALUE error"

Can you try explaining this a different way?

I also don' tknow what the "instances-item erors" would be?


Hi! back.. been working bottom / middle post? ugh.. hope this is working
(should have picked up using bottom post from example..) but sorry error was
intermittent, but this answer will be handy from looking for an error 1 line
at a time. thanks for the patience.. long formula/problem..

match function seems to work only from range of very top row, down (per help
file).
showing hyperlink formula maybe necessary, but problem with isolating top
rows out of MATCH search to remove 2 types of items (1. if have same
CHARACTER in a cell, same column, for other setting / key search purposes,
with hyperlinks;

2. for this problem: searching for error, also in header/same column, have
cell with "Running Total" of same "CHARACTER", as above, "character" not
"the" problem in this instance. The Running Total formula at top, comes up
if coincident, with same error as row searching for, below. The formula in
header for running totals in header is:

=SUMPRODUCT(--(EXACT(LEFT($AH$110:$AH$1292,1),{"A","B","C"})))
in say: row 15

need to have method of isolating errors out of hyperlink formula, so search
goes to subsequent error.

although I came up with the idea to use hyperlink, etc. have been taking
this one step.. here I am :) if interested, next step/ oops forbid, would
answer be in desired use of hyperlink: although match finds first instance,
WOULD RATHER FIND:

- next instance down, same column from where cursor currently resides.
hope that helps. as it is, not sure if answer exists. trying to keep from
cutting too many items away from column working in. thanks

"Paul Lautman" wrote:

nastech wrote:
060716 MATCH function, exclusion question

hi, trying to use MATCH to find the first instance of items, but
want to exlude the top of document where there may be one or more of
the "instances- item erors"

the following does not work for receiving a #VALUE error.
e.g.:
AND(ROW()=$B$56,MATCH("dd",$D$1:$D$1303,0))


full example in use: is for hyperlink to first instance:

=HYPERLINK(IF(ISNA(MATCH("X",$D$1:$D$1303,10000)), "",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",W12),"$",""),ROW(),"")&
AND(ROW()=$B$56,MATCH("dd",$D$1:$D$1303,0))),$W$1 ,0))),"dd")


notes:
- remove AND(ROW()=$B$56, and following: ")"
- to work: W12, is column where want link to end up, 12 is row
this formula resides. thanks

$B$56 isn't a row!
ROW()=56
is how you should write something like that

sorry, p.s.: using the 1st portion "for not:)" row() <header, does not
seem to work, as I think it is saying: if there is a row I am specifying
above, make the whole formula, not work. either I have my exclusions wrong,
or can not exclude with MATCH. ??