I have data stored on a sheet that I want to access from another sheet.
The data is stored in rows with a varying number of data items on each
row. Is there a worksheet formula to return the value in the last
occupied cell on the row?

Any help would be appreciated.

Hi
try something like

with worksheets("other sheet")
msgbox .Cells(Rows.count, "A").End(xlUp).value
end with

-----Original Message-----
I have data stored on a sheet that I want to access from
another sheet.
The data is stored in rows with a varying number of
data items on each
row. Is there a worksheet formula to return the value in
the last
occupied cell on the row?

Any help would be appreciated.

.

Thanks for the response Frank, but I was hoping to be able to do this
with a worsheet function, rather than with VBA?



Frank Kabel wrote:

Hi
try something like

with worksheets("other sheet")
msgbox .Cells(Rows.count, "A").End(xlUp).value
end with


-----Original Message-----
I have data stored on a sheet that I want to access from

another sheet.

The data is stored in rows with a varying number of

data items on each

row. Is there a worksheet formula to return the value in

the last

occupied cell on the row?

Any help would be appreciated.

.

Hi
sorry, as you posted in the .programming group I assumed
you wanted a VBA solution (shopuld have read your question
more carefully).
Find below a couple of possible formulas (depending on the
type of your data):
-------------------
A. Collection of formulas to return the last value in a
COLUMN depending on the type/structure of your data.
1. If you have no blank rows in between use
=OFFSET($A$1,COUNTA($A:$A)-1,0)

2. If you have blank rows in between try the following
depending of the type of values in your column:
2.a. If you have ONLY text values in column A try
=INDEX(A:A,MATCH(REPT("z",255),A:A))

2.b. If you have ONLY numbers in column A:
=INDEX(A:A,MATCH(9.99999999999999E307,A:A))
or
=LOOKUP(9.99999999999999E307,A:A)

2.c. If you have BOTH types (text and values), but AT
LEAST one text
and one numeric entry
=INDEX(A:A,MAX(MATCH(9.99999999999999E307,A:A),MAT CH(REPT
("z",255),A:A)))

2.d. If you don't know the type of data use the following
array
function (entered with CTRL+SHIFT+ENTER)
=INDEX(A:A,MAX(IF(ISBLANK(A:A),0,ROW(A:A))))

2.e If you don't want to count formula results like ="" as
entry adapt
2.d. as follows:
=INDEX(A:A,MAX(IF(A:A<"",0,ROW(A:A))))

----------

B. Collection of formulas to return the last value in a
ROW depending on the type/structure of your data:
1. If you have no blank columns in between use
=OFFSET($A$1,0,COUNTA($1:$1)-1)

2. If you have blank columns in between try the following
depending of the type of values in your row:
2.a. If you have ONLY text values in column A try
=INDEX(1:1,1,MATCH(REPT("z",255),1:1))

2.b. If you have ONLY numbers in column A:
=INDEX(1:1,1,MATCH(9.99999999999999E307,1:1))
or
=LOOKUP(9.99999999999999E307,1:1)

2.c. If you have BOTH types (text and values), but AT
LEAST one text and one numeric entry
=INDEX(1:1,1,MAX(MATCH(9.99999999999999E307,1:1),M ATCH(REPT
("z",255),1:1)))

2.d. If you don't know the type of data use the following
array function (entered with CTRL+SHIFT+ENTER)
=INDEX(1:1,1,MAX(IF(ISBLANK(1:1),0,COLUMN(1:1))))

2.e If you don't want to count formula results like ="" as
entry adapt 2.d. as follows:
=INDEX(1:1,1,MAX(IF(1:1<"",0,COLUMN(1:1))))


--
Regards
Frank Kabel
Frankfurt, Germany

-----Original Message-----
Thanks for the response Frank, but I was hoping to be
able to do this
with a worsheet function, rather than with VBA?



Frank Kabel wrote:

Hi
try something like

with worksheets("other sheet")
msgbox .Cells(Rows.count, "A").End(xlUp).value
end with


-----Original Message-----
I have data stored on a sheet that I want to access
from

another sheet.

The data is stored in rows with a varying number of

data items on each

row. Is there a worksheet formula to return the value
in

the last

occupied cell on the row?

Any help would be appreciated.

.


.

thanks Frank... got it now.

Frank Kabel wrote:

Hi
sorry, as you posted in the .programming group I assumed
you wanted a VBA solution (shopuld have read your question
more carefully).
Find below a couple of possible formulas (depending on the
type of your data):
-------------------
A. Collection of formulas to return the last value in a
COLUMN depending on the type/structure of your data.
1. If you have no blank rows in between use
=OFFSET($A$1,COUNTA($A:$A)-1,0)

2. If you have blank rows in between try the following
depending of the type of values in your column:
2.a. If you have ONLY text values in column A try
=INDEX(A:A,MATCH(REPT("z",255),A:A))

2.b. If you have ONLY numbers in column A:
=INDEX(A:A,MATCH(9.99999999999999E307,A:A))
or
=LOOKUP(9.99999999999999E307,A:A)

2.c. If you have BOTH types (text and values), but AT
LEAST one text
and one numeric entry
=INDEX(A:A,MAX(MATCH(9.99999999999999E307,A:A),MAT CH(REPT
("z",255),A:A)))

2.d. If you don't know the type of data use the following
array
function (entered with CTRL+SHIFT+ENTER)
=INDEX(A:A,MAX(IF(ISBLANK(A:A),0,ROW(A:A))))

2.e If you don't want to count formula results like ="" as
entry adapt
2.d. as follows:
=INDEX(A:A,MAX(IF(A:A<"",0,ROW(A:A))))

----------

B. Collection of formulas to return the last value in a
ROW depending on the type/structure of your data:
1. If you have no blank columns in between use
=OFFSET($A$1,0,COUNTA($1:$1)-1)

2. If you have blank columns in between try the following
depending of the type of values in your row:
2.a. If you have ONLY text values in column A try
=INDEX(1:1,1,MATCH(REPT("z",255),1:1))

2.b. If you have ONLY numbers in column A:
=INDEX(1:1,1,MATCH(9.99999999999999E307,1:1))
or
=LOOKUP(9.99999999999999E307,1:1)

2.c. If you have BOTH types (text and values), but AT
LEAST one text and one numeric entry
=INDEX(1:1,1,MAX(MATCH(9.99999999999999E307,1:1),M ATCH(REPT
("z",255),1:1)))

2.d. If you don't know the type of data use the following
array function (entered with CTRL+SHIFT+ENTER)
=INDEX(1:1,1,MAX(IF(ISBLANK(1:1),0,COLUMN(1:1))))

2.e If you don't want to count formula results like ="" as
entry adapt 2.d. as follows:
=INDEX(1:1,1,MAX(IF(1:1<"",0,COLUMN(1:1))))


--
Regards
Frank Kabel
Frankfurt, Germany


-----Original Message-----
Thanks for the response Frank, but I was hoping to be

able to do this

with a worsheet function, rather than with VBA?



Frank Kabel wrote:


Hi
try something like

with worksheets("other sheet")
msgbox .Cells(Rows.count, "A").End(xlUp).value
end with



-----Original Message-----
I have data stored on a sheet that I want to access

from

another sheet.


The data is stored in rows with a varying number of

data items on each


row. Is there a worksheet formula to return the value

in

the last


occupied cell on the row?

Any help would be appreciated.

.


.