Replace * by other text that would always sort to the end of the list
e.g. a series of z's or better a unicode character such as [alpha] by
using InsertSymbol and copying. * only acts as wildcard when the last
argument of match or vlookup is 0 otherwise this this will be
interpreted using standard text ordering

driller wrote:

excuse me beecher,
Hi macropod, i test the 3 formulas
the first one spots the last number perfectly
the second and third one do not looks for the right spot.

i test a column Y with data per cell (i.e. 1,2,3,2x,6,8,9,3x,5). it seems my
excel is not responding to spot the last text 3x.
please explain ?

"macropod" wrote:

Hi beecher,

To find the row with the last numeric value in column Y, you could use:
=MATCH(1E+306,Y:Y,1)
To find the row with the last text value in column Y, you could use:
=MATCH("*",Y:Y,-1)
To find the row with the last alphanumeric value in column Y, you could use:
=MAX(IF(ISERROR(MATCH("*",Y:Y,-1)),0,MATCH("*",Y:Y,-1)),IF(ISERROR(MATCH(1E+
306,Y:Y,1)),0,MATCH(1E+306,Y:Y,1)))

Cheers

--
macropod
[MVP - Microsoft Word]


"beecher" wrote in message
...
I am multiple worksheets and am running into a problem. The formula I
have
utilizes constants (i.e. $Y$4) to demarcate the last cell in a column.
For
instance, if the column expanded from Y1-Y100 I would use $Y$100 in my
formula for each cell in another column (i.e. column Z). However, each of
my
worksheets has a different number of rows such that the column in question
has varying length. Sometimes the column stretches from Y1-Y40, other
times
from Y1-Y2000. Is there a quick way I can convert the last cell in each
column into a constant. This may help:

I have I would like
Y1-Y100 $Y$100
Y1-Y2000 $Y$2000
Y1-Y53 $Y$53

Thanks a lot,
beecher