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