Newbie Question: Returning the value from a cell on another sheet
 

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.

Newbie Question: Returning the value from a cell on another sheet
 

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.

.

Newbie Question: Returning the value from a cell on another sheet
 

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.

.

Newbie Question: Returning the value from a cell on another sheet
 

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.

.


.

Newbie Question: Returning the value from a cell on another sheet
 

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.

.


.