conversion of variables to constants
 

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

conversion of variables to constants
 

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

conversion of variables to constants
 

since you already established a formula to look for the value of the last
cell in a column...
in sheet 1 : go to $Y$4 as a home cell for the value of the last cell in
column Y.
then with your mouse, place your cursor in the Name Box type a name like
"BEE1"
for other sheets you have to do the same with a suffix related to
sheetnumber, for easy tracking of your named constant.

"beecher" wrote:

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

conversion of variables to constants
 

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

conversion of variables to constants
 

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

conversion of variables to constants
 

Hi driller,

The 1st formula tests the column for *numbers* only, including numbers
generated by formulae. If your column has both numbers and text strings,
then only the last row with a *number* will be returned.
The 2nd formula tests the column for *text strings* only, including text
strings generated by formulae. If your column has both numbers and text
strings, then only the last row with a *text string* will be returned.
The 3rd formula tests the column for *numbers and text strings*, including
numbers and text strings generated by formulae. If your column has both
numbers and text strings, then the last row with either a *number or a text
string* will be returned.

I should have mentioned that the 2nd and 3rd versions may fail if there are
cells with nul values in the range.

Cheers
--
macropod
[MVP - Microsoft Word]


"driller" wrote in message
...
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