hi, am wondering how to find the last copy of a text in a column, e.g.:
"fred"
any ideas? thanks

Do you mean there are multiple instances of "Fred" and you want to find the
last instance?

What exactly do you want to do when you find the last instance?

...........A..........B
1.....Fred.......10
2.....Biff.........22
3.....Fred.......17
4.....Fred.......11
5.....Lisa........50

This formula finds the last instance of "Fred" and returns the corresponding
value from column B:

=LOOKUP(2,1/(A1:A5="Fred"),B1:B5)

--
Biff
Microsoft Excel MVP


"nastech" wrote in message
...
hi, am wondering how to find the last copy of a text in a column, e.g.:
"fred"
any ideas? thanks

hi, thanks for the reponse, saw previous similar question using word fred,
got got a response, :) this was my nth posting.. last occurance of fred is
the idea.
other info is:

hi, trying to find way to locate text "up" from current position. not sure
which function / how to use. have been looking at: MATCH, LOOKUP.. thanks

example have for find last number in a column is:
=P54=LOOKUP(9.99999999999999E+307,P$24:P$59)

-----

example using for finding next instance "down" from current position is:
MATCH(TRUE,OFFSET($AT427:$AT$1231,1,0)="dn",0)

array portion: guessing would be able to search from fixed top position of
document, down to current position. e.g.: $AT$128:$AT427,-1

***
will be using range before "current" cell/ position to find the letters: "dn"


"T. Valko" wrote:

Do you mean there are multiple instances of "Fred" and you want to find the
last instance?

What exactly do you want to do when you find the last instance?

...........A..........B
1.....Fred.......10
2.....Biff.........22
3.....Fred.......17
4.....Fred.......11
5.....Lisa........50

This formula finds the last instance of "Fred" and returns the corresponding
value from column B:

=LOOKUP(2,1/(A1:A5="Fred"),B1:B5)

--
Biff
Microsoft Excel MVP


"nastech" wrote in message
...
hi, am wondering how to find the last copy of a text in a column, e.g.:
"fred"
any ideas? thanks

just looking for fred, not different column data, is that what you have?


"T. Valko" wrote:

Do you mean there are multiple instances of "Fred" and you want to find the
last instance?

What exactly do you want to do when you find the last instance?

...........A..........B
1.....Fred.......10
2.....Biff.........22
3.....Fred.......17
4.....Fred.......11
5.....Lisa........50

This formula finds the last instance of "Fred" and returns the corresponding
value from column B:

=LOOKUP(2,1/(A1:A5="Fred"),B1:B5)

--
Biff
Microsoft Excel MVP


"nastech" wrote in message
...
hi, am wondering how to find the last copy of a text in a column, e.g.:
"fred"
any ideas? thanks

looking for cell reference to "last" occurance of text, in 1 column. e.g.:
"fred"

"T. Valko" wrote:

Do you mean there are multiple instances of "Fred" and you want to find the
last instance?

What exactly do you want to do when you find the last instance?

...........A..........B
1.....Fred.......10
2.....Biff.........22
3.....Fred.......17
4.....Fred.......11
5.....Lisa........50

This formula finds the last instance of "Fred" and returns the corresponding
value from column B:

=LOOKUP(2,1/(A1:A5="Fred"),B1:B5)

--
Biff
Microsoft Excel MVP


"nastech" wrote in message
...
hi, am wondering how to find the last copy of a text in a column, e.g.:
"fred"
any ideas? thanks

Ok...

A1...Fred
A2...Biff
A3...Fred
A4...Fred
A5...Lisa

="A"&LOOKUP(2,1/(A1:A5="Fred"),ROW(A1:A5))

Returns: A4

--
Biff
Microsoft Excel MVP


"nastech" wrote in message
...
looking for cell reference to "last" occurance of text, in 1 column.
e.g.:
"fred"

"T. Valko" wrote:

Do you mean there are multiple instances of "Fred" and you want to find
the
last instance?

What exactly do you want to do when you find the last instance?

...........A..........B
1.....Fred.......10
2.....Biff.........22
3.....Fred.......17
4.....Fred.......11
5.....Lisa........50

This formula finds the last instance of "Fred" and returns the
corresponding
value from column B:

=LOOKUP(2,1/(A1:A5="Fred"),B1:B5)

--
Biff
Microsoft Excel MVP


"nastech" wrote in message
...
hi, am wondering how to find the last copy of a text in a column, e.g.:
"fred"
any ideas? thanks

hi, thanks.. I got that to work, trying to incorporate that into a hyperlink
formula that is very useful for moving down a document; I was trying to
reverse the idea, to move up a document.

first example works for down, keys on: "dn" that col.
2nd example problem: looking for an UP formula that keys off same "dn", in
that same column. will put up formula in next column to right, same row.
thanks.

1st example, for moving down a document seems to work well, but do you think
it was properly done..? anyways, note: ctrl-shift-enter array / does not
cut paste well (at least if calculation is turned off). working down example
is:

=HYPERLINK(IF(ISNA(INDEX(ROW(OFFSET($AT141:$AT$123 2,1,0))-ROW(OFFSET($AT141,1,0)),
MATCH(TRUE,OFFSET($AT141:$AT$1232,1,0)="dn",0)))," ",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",$A141),"$",""),ROW(),"")&IND EX(ROW(OFFSET($AT141:$AT$1232,1,0)),
MATCH(TRUE,OFFSET($AT141:$AT$1232,1,0)="dn",0))),$ Z$3,1))),"dn")


NOT WORKING UP Example is:
problems might be too many row(offsets ?
note: using start of document rows / use of "$" signs, and -1's in offset

=HYPERLINK(IF(ISNA(INDEX(ROW(OFFSET($AT$129:$AT141 ,-1,0))-ROW(OFFSET($AT141,-1,0)),
LOOKUP(2,1/OFFSET($AT$129:$AT141,-1,0)="dn"),ROW(OFFSET($AT$129:$AT141,-1,0)))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",$A141),"$",""),ROW(),"")&IND EX(ROW(OFFSET($A$129:$AT141,-1,0)),
LOOKUP(2,1/OFFSET($AT$129:$AT141,-1,0)="dn"),ROW(OFFSET($AT$129:$AT141,-1,0)))),-1,1))),"u")

XXXXXXXXX

"T. Valko" wrote:

Ok...

A1...Fred
A2...Biff
A3...Fred
A4...Fred
A5...Lisa

="A"&LOOKUP(2,1/(A1:A5="Fred"),ROW(A1:A5))

Returns: A4

--
Biff
Microsoft Excel MVP


"nastech" wrote in message
...
looking for cell reference to "last" occurance of text, in 1 column.
e.g.:
"fred"

"T. Valko" wrote:

Do you mean there are multiple instances of "Fred" and you want to find
the
last instance?

What exactly do you want to do when you find the last instance?

...........A..........B
1.....Fred.......10
2.....Biff.........22
3.....Fred.......17
4.....Fred.......11
5.....Lisa........50

This formula finds the last instance of "Fred" and returns the
corresponding
value from column B:

=LOOKUP(2,1/(A1:A5="Fred"),B1:B5)

--
Biff
Microsoft Excel MVP


"nastech" wrote in message
...
hi, am wondering how to find the last copy of a text in a column, e.g.:
"fred"
any ideas? thanks

$Z$3 is the amount of rows down, for the offset.

hi, example working with, that seems to be correct for what trying to do, is:

=LOOKUP(2,1/(OFFSET($AT$129:$AT141,-1,0)="dn"),ROW(OFFSET($AT$129:$AT141,-1,0)))

placed in row 141, that has a "dn", is not included,
and result: 29 is correct (prev row with "dn")

trying to place in formula, does not seem to take / allow to enter:
purpose of formula is for shortcut up to next text occurance of "dn".

=HYPERLINK(IF(ISNA(INDEX(ROW(OFFSET($AT$129:$AT141 ,-1,0))-ROW(OFFSET($AT141,-1,0)),
LOOKUP(2,1/(OFFSET($AT$129:$AT141,-1,0)="dn"),ROW(OFFSET($AT$129:$AT141,-1,0))))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",$A141),"$",""),ROW(),"")&IND EX(ROW(OFFSET($A$129:$AT141,-1,0)),
LOOKUP(2,1/(OFFSET($AT$129:$AT141,-1,0)="dn"),ROW(OFFSET($AT$129:$AT141,-1,0))))),-1,1))),"u")

Sorry, but I have no idea what you're trying to do with this.

--
Biff
Microsoft Excel MVP


"nastech" wrote in message
...
hi, example working with, that seems to be correct for what trying to do,
is:

=LOOKUP(2,1/(OFFSET($AT$129:$AT141,-1,0)="dn"),ROW(OFFSET($AT$129:$AT141,-1,0)))

placed in row 141, that has a "dn", is not included,
and result: 29 is correct (prev row with "dn")

trying to place in formula, does not seem to take / allow to enter:
purpose of formula is for shortcut up to next text occurance of "dn".

=HYPERLINK(IF(ISNA(INDEX(ROW(OFFSET($AT$129:$AT141 ,-1,0))-ROW(OFFSET($AT141,-1,0)),
LOOKUP(2,1/(OFFSET($AT$129:$AT141,-1,0)="dn"),ROW(OFFSET($AT$129:$AT141,-1,0))))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",$A141),"$",""),ROW(),"")&IND EX(ROW(OFFSET($A$129:$AT141,-1,0)),
LOOKUP(2,1/(OFFSET($AT$129:$AT141,-1,0)="dn"),ROW(OFFSET($AT$129:$AT141,-1,0))))),-1,1))),"u")

have 2 hyperlink formulas, making for use "in-document" that allows a
shortcut jump "down" to the next copy of the same hyperlink (keys off the
friendly name: "dn"). where the down hyperlink is working, I was trying to
develope a hyperlink that jumps up to the last "dn" - above the current line
that also holds the down link. the right link is placed in the column to the
right, or column AU, any column same row, doesn't matter. I don't have the
"up" link working yet. the base formula (last item below) is working.
problem is in setup of hyperlink.. thanks.

have an extensive example of a hyperlink, but should be easy to see
portions, with line returns to help separate sections.. thanks in advance

version that works for "dn" shortcut, (to next copy of this link) is in fact
what the 2nd (non-workin) version is keying off.
note: cntrl-shift-enter array, $z$3 is number of rows to move down (size
of screen), placed in row 141

=HYPERLINK(IF(ISNA(INDEX(ROW(OFFSET($AT141:$AT$123 2,1,0))-ROW(OFFSET($AT141,1,0)),
MATCH(TRUE,OFFSET($AT141:$AT$1232,1,0)="dn",0)))," ",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",$A141),"$",""),ROW(),"")&IND EX(ROW(OFFSET($AT141:$AT$1232,1,0)),
MATCH(TRUE,OFFSET($AT141:$AT$1232,1,0)="dn",0))),$ Z$3,1))),"dn")


Modified version that is not quite right, can not enter / am probably using
functions / variables not needed ??? thanks, non-working example is:
note: placed in row 141, column to right, AU

=HYPERLINK(IF(ISNA(INDEX(ROW(OFFSET($AT$129:$AT141 ,-1,0))-ROW(OFFSET($AT141,-1,0)),
LOOKUP(2,1/(OFFSET($AT$129:$AT141,-1,0)="dn"),ROW(OFFSET($AT$129:$AT141,-1,0))))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",$A141),"$",""),ROW(),"")&IND EX(ROW(OFFSET($A$129:$AT141,-1,0)),
LOOKUP(2,1/(OFFSET($AT$129:$AT141,-1,0)="dn"),ROW(OFFSET($AT$129:$AT141,-1,0))))),-1,1))),"u")


base formula working with, that seems to be working: row 141 is not
included in query, previous / last occurance of "dn" is correctly identified.

=LOOKUP(2,1/(OFFSET($AT$129:$AT141,-1,0)="dn"),ROW(OFFSET($AT$129:$AT141,-1,0)))