Reference the last cell in a column
 

Hello.
I am looking to reference the last cell that contains data from one sheet
onto another sheet in my workbook. The issue I am having with this is that
this cell will change (however I can limit the reference to a specific
range).
Is there a way to dynamically reference a range of cells without having to
go back and adjust the lookup manually?

Reference the last cell in a column
 

Take a look at the offset function. You'd need something like this

=OFFSET(A1,0,COUNT(Sheet1:A:A),1,1)

Here is some explanation on OFFSET

http://www.mvps.org/dmcritchie/excel/offset.htm
--
HTH,
Barb Reinhardt



"Wh0079" wrote:

Hello.
I am looking to reference the last cell that contains data from one sheet
onto another sheet in my workbook. The issue I am having with this is that
this cell will change (however I can limit the reference to a specific
range).
Is there a way to dynamically reference a range of cells without having to
go back and adjust the lookup manually?

Reference the last cell in a column
 

On Thu, 21 Aug 2008 17:28:10 -0700, Wh0079
wrote:

Hello.
I am looking to reference the last cell that contains data from one sheet
onto another sheet in my workbook. The issue I am having with this is that
this cell will change (however I can limit the reference to a specific
range).
Is there a way to dynamically reference a range of cells without having to
go back and adjust the lookup manually?

This formula will return the last value in column C:

=LOOKUP(2,1/(LEN(C:C)0),C:C)

In versions prior to Excel 2007, you may not be able to reference the entire
column; so you might need to change the reference to C1:C65535.
--ron

Reference the last cell in a column
 

Is it always the same column and can there be any black cells in-between?

If there can be no empty cells within the range and it is the same column
you can simply use


=INDEX(Sheet2!A:A,COUNTA(Sheet2!A:A))



if there can be empty cells and it is a number you can use


=LOOKUP(99^99,Sheet2!A:A)


if number or test


=LOOKUP(2,1/(Sheet2!A1:A50000<""),Sheet2!A1:A50000)






--


Regards,


Peo Sjoblom



"Wh0079" wrote in message
...
Hello.
I am looking to reference the last cell that contains data from one sheet
onto another sheet in my workbook. The issue I am having with this is that
this cell will change (however I can limit the reference to a specific
range).
Is there a way to dynamically reference a range of cells without having to
go back and adjust the lookup manually?

Reference the last cell in a column
 

Wow guys.
Thanks!
I sent the question just before I left yesterday and have been doing other
tasks but I am about to try your suggestions. I will let you know which seems
to work the best but thanks to all three for the suggestions!

"Peo Sjoblom" wrote:

Is it always the same column and can there be any black cells in-between?

If there can be no empty cells within the range and it is the same column
you can simply use


=INDEX(Sheet2!A:A,COUNTA(Sheet2!A:A))



if there can be empty cells and it is a number you can use


=LOOKUP(99^99,Sheet2!A:A)


if number or test


=LOOKUP(2,1/(Sheet2!A1:A50000<""),Sheet2!A1:A50000)






--


Regards,


Peo Sjoblom



"Wh0079" wrote in message
...
Hello.
I am looking to reference the last cell that contains data from one sheet
onto another sheet in my workbook. The issue I am having with this is that
this cell will change (however I can limit the reference to a specific
range).
Is there a way to dynamically reference a range of cells without having to
go back and adjust the lookup manually?

Reference the last cell in a column
 

OK folks, I guess I am not quite there yet. I am sure the answer I need is
contained in once (if not all) of your three answers however, since I am a
relative novice with writing formulas, I still am getting #values! in each of
the three solutions attempted.
Let me see if I can be a bit more descriptive in the problem I am trying to
solve...

If I have the following data in one sheet...

Sheet 2

COLUMN

A B C D E F G H I
ROW
1 Balance
2 Vacation Sick Floater
3 107.00 582.23 0.00
4 123.67 588.90 0.00
5
6
7

The row numbers indicate months (3=July, 4=August, etc.)
I have set something up on (let's say sheet 6) to where, I have a summary of
hours worked, including OT, sick if used, etc.
I thought it might be nice if I went ahead and included the vacation and
sick balances in this slip of paper I give my people but of course the data
that just needs to be captured (nothing else, however in the cells that need
capturing, they are formula cells as well so I just want to capture the
values from those cells)

Now (of course) if the rows are representing months, that would represent
the range to which I want the last row's cell captured

Any additional tips or if any one of the formulas you guys provided could be
further broken down for us slow people would be appreciated very much!



"Peo Sjoblom" wrote:

Is it always the same column and can there be any black cells in-between?

If there can be no empty cells within the range and it is the same column
you can simply use


=INDEX(Sheet2!A:A,COUNTA(Sheet2!A:A))



if there can be empty cells and it is a number you can use


=LOOKUP(99^99,Sheet2!A:A)


if number or test


=LOOKUP(2,1/(Sheet2!A1:A50000<""),Sheet2!A1:A50000)






--


Regards,


Peo Sjoblom



"Wh0079" wrote in message
...
Hello.
I am looking to reference the last cell that contains data from one sheet
onto another sheet in my workbook. The issue I am having with this is that
this cell will change (however I can limit the reference to a specific
range).
Is there a way to dynamically reference a range of cells without having to
go back and adjust the lookup manually?

Reference the last cell in a column
 

Oops, after re-reading my reply, I noticed that I had said "the last cell in
the ROW" which is inaccurate.... it's the last cell in a particular COLUNM

"Wh0079" wrote:

OK folks, I guess I am not quite there yet. I am sure the answer I need is
contained in once (if not all) of your three answers however, since I am a
relative novice with writing formulas, I still am getting #values! in each of
the three solutions attempted.
Let me see if I can be a bit more descriptive in the problem I am trying to
solve...

If I have the following data in one sheet...

Sheet 2

COLUMN

A B C D E F G H I
ROW
1 Balance
2 Vacation Sick Floater
3 107.00 582.23 0.00
4 123.67 588.90 0.00
5
6
7

The row numbers indicate months (3=July, 4=August, etc.)
I have set something up on (let's say sheet 6) to where, I have a summary of
hours worked, including OT, sick if used, etc.
I thought it might be nice if I went ahead and included the vacation and
sick balances in this slip of paper I give my people but of course the data
that just needs to be captured (nothing else, however in the cells that need
capturing, they are formula cells as well so I just want to capture the
values from those cells)

Now (of course) if the rows are representing months, that would represent
the range to which I want the last row's cell captured

Any additional tips or if any one of the formulas you guys provided could be
further broken down for us slow people would be appreciated very much!



"Peo Sjoblom" wrote:

Is it always the same column and can there be any black cells in-between?

If there can be no empty cells within the range and it is the same column
you can simply use


=INDEX(Sheet2!A:A,COUNTA(Sheet2!A:A))



if there can be empty cells and it is a number you can use


=LOOKUP(99^99,Sheet2!A:A)


if number or test


=LOOKUP(2,1/(Sheet2!A1:A50000<""),Sheet2!A1:A50000)






--


Regards,


Peo Sjoblom



"Wh0079" wrote in message
...
Hello.
I am looking to reference the last cell that contains data from one sheet
onto another sheet in my workbook. The issue I am having with this is that
this cell will change (however I can limit the reference to a specific
range).
Is there a way to dynamically reference a range of cells without having to
go back and adjust the lookup manually?

Reference the last cell in a column
 

On Fri, 22 Aug 2008 14:36:01 -0700, Wh0079
wrote:

Oops, after re-reading my reply, I noticed that I had said "the last cell in
the ROW" which is inaccurate.... it's the last cell in a particular COLUNM

You explanation isn't any more clear, to me, than your original request.

What happened when you tried the formulas you were given? As written, they
will all return the last entry in a designated column, with certain limitations
as described by the various posters.

How did you apply them?

What was the result?

What was your desired result?
--ron