Since the address of the last cell keeps changing as data are entered, how
can I link to this last cell from another workbook?

Thanks.

zhj23

The best way is to give the cell in the otherworkbook a name. Do this by
selecting the cell and then typing the name into the Name Box. The name box
is that area that will be displaying the address of the cell you've selected.
Click in the Name Box and give it a name such as myLastCell and end the
entry by pressing the [Enter] key - that's important.

Then in the other workbook, you can link to it as =Book1!myLastCell
and Excel will keep up with it no matter where it gets shoved around to.
For more information check out Excel Help on
Define named cell references or ranges
and
Name a cell or a range of cells


"zhj23" wrote:

Since the address of the last cell keeps changing as data are entered, how
can I link to this last cell from another workbook?

Thanks.

zhj23

Thanks for the prompt reply.

When I did exactly what you told: I call A1 as MyLastCell, and entered a
value of 5 (say), when I enter another value 8 in A2, I would expect my last
cell value to be 8. It doesnt. MyLastCell still equlas to 5.

May be I missed some of your points .... sorry.

zhj23

"JLatham" wrote:

The best way is to give the cell in the otherworkbook a name. Do this by
selecting the cell and then typing the name into the Name Box. The name box
is that area that will be displaying the address of the cell you've selected.
Click in the Name Box and give it a name such as myLastCell and end the
entry by pressing the [Enter] key - that's important.

Then in the other workbook, you can link to it as =Book1!myLastCell
and Excel will keep up with it no matter where it gets shoved around to.
For more information check out Excel Help on
Define named cell references or ranges
and
Name a cell or a range of cells


"zhj23" wrote:

Since the address of the last cell keeps changing as data are entered, how
can I link to this last cell from another workbook?

Thanks.

zhj23

Nope, actually it sounds like you followed my instructions properly to a
successful conclusion. Unfortunately I gave you bad instructions. I
presumed you were inserting new information ahead of the last cell (either
new rows or columns) which would have kept pushing myLastCell over/down the
sheet and keeping it in the last position.

I'm not sure the following will assist you or not, depends on whether you
need to find the last cell in one worksheet in one particular workbook or
not. You could use this User Defined Function (UDF) to get a best guess at
that address. I say 'best guess' because UsedRange on a sheet is not always
accurate. It reports the last cell (in right-most column and largest
numbered row ever used) on the sheet and if you've deleted columns/rows, it
can be inaccurate.

Use [Alt]+[F11] to open the VB editor, then use the menu in it to Insert |
Module. Cut and paste the code below into it and modify it to refer to the
correct workbook and sheet in it. Then close the VB Editor.

Function FindLastForeignCellValue() As Variant
Dim tmpResult As String
On Error GoTo NoAnswer
tmpResult = _
Workbooks("OtherWorkbook.xls"). _
Worksheets("theWorksheet").UsedRange.Address
tmpResult = _
Right(tmpResult, Len(tmpResult) - _
InStr(tmpResult, ":"))
FindLastForeignCellValue = _
Workbooks("OtherWorkbook.xls"). _
Worksheets("theWorksheet").Range(tmpResult).Value
ExitFindLast:
Exit Function
NoAnswer:
FindLastForeignCellValue = ""
Resume ExitFindLast
End Function

to actually use this, put a formula like this into a cell:
=FindLastForeignCellValue()
It will probably change to all lowercase, that's normal.

You can also use it inside of a more complex formula such as
=A7 + 42 - FindLastForeignCellValue()

If you are interested in the last used cell in a particular row or column,
then it is a different ballgame.

Caveat: the function will only return a value when the other workbook is
also open.

And to get rid of that MyLastCell definition in your workbook, use Insert |
Name | Define and choose the name and hit the [Delete] button. It will be
removed as a definition.


"zhj23" wrote:

Thanks for the prompt reply.

When I did exactly what you told: I call A1 as MyLastCell, and entered a
value of 5 (say), when I enter another value 8 in A2, I would expect my last
cell value to be 8. It doesnt. MyLastCell still equlas to 5.

May be I missed some of your points .... sorry.

zhj23

"JLatham" wrote:

The best way is to give the cell in the otherworkbook a name. Do this by
selecting the cell and then typing the name into the Name Box. The name box
is that area that will be displaying the address of the cell you've selected.
Click in the Name Box and give it a name such as myLastCell and end the
entry by pressing the [Enter] key - that's important.

Then in the other workbook, you can link to it as =Book1!myLastCell
and Excel will keep up with it no matter where it gets shoved around to.
For more information check out Excel Help on
Define named cell references or ranges
and
Name a cell or a range of cells


"zhj23" wrote:

Since the address of the last cell keeps changing as data are entered, how
can I link to this last cell from another workbook?

Thanks.

zhj23

Thank you very much for your patience.

I found the following codes in a book which is very close to what I want:

Function LASTINCOLUMN(rng As Range)
Dim LastCell As Range

Application.Volatile
Set LastCell = rng.Parent.Cells(Rows.Count, rng.Column) _
.End(xlUp)
LASTINCOLUMN = LastCell.Value
If IsEmpty(LastCell) Then LASTINCOLUMN = ""
If rng.Parent.Cells(Rows.Count, rng.Column) < "" Then _
LASTINCOLUMN = rng.Parent.Cells(Rows.Count, rng.Column)
End Function

Example: In a given range c1:h10, LastInColumn(c1:h10) will return the last
non-empty cell value in column C. If the program can be modified to give the
last non-empty cell value in column H, that will be fine with me.


zhj23

"JLatham" wrote:

Nope, actually it sounds like you followed my instructions properly to a
successful conclusion. Unfortunately I gave you bad instructions. I
presumed you were inserting new information ahead of the last cell (either
new rows or columns) which would have kept pushing myLastCell over/down the
sheet and keeping it in the last position.

I'm not sure the following will assist you or not, depends on whether you
need to find the last cell in one worksheet in one particular workbook or
not. You could use this User Defined Function (UDF) to get a best guess at
that address. I say 'best guess' because UsedRange on a sheet is not always
accurate. It reports the last cell (in right-most column and largest
numbered row ever used) on the sheet and if you've deleted columns/rows, it
can be inaccurate.

Use [Alt]+[F11] to open the VB editor, then use the menu in it to Insert |
Module. Cut and paste the code below into it and modify it to refer to the
correct workbook and sheet in it. Then close the VB Editor.

Function FindLastForeignCellValue() As Variant
Dim tmpResult As String
On Error GoTo NoAnswer
tmpResult = _
Workbooks("OtherWorkbook.xls"). _
Worksheets("theWorksheet").UsedRange.Address
tmpResult = _
Right(tmpResult, Len(tmpResult) - _
InStr(tmpResult, ":"))
FindLastForeignCellValue = _
Workbooks("OtherWorkbook.xls"). _
Worksheets("theWorksheet").Range(tmpResult).Value
ExitFindLast:
Exit Function
NoAnswer:
FindLastForeignCellValue = ""
Resume ExitFindLast
End Function

to actually use this, put a formula like this into a cell:
=FindLastForeignCellValue()
It will probably change to all lowercase, that's normal.

You can also use it inside of a more complex formula such as
=A7 + 42 - FindLastForeignCellValue()

If you are interested in the last used cell in a particular row or column,
then it is a different ballgame.

Caveat: the function will only return a value when the other workbook is
also open.

And to get rid of that MyLastCell definition in your workbook, use Insert |
Name | Define and choose the name and hit the [Delete] button. It will be
removed as a definition.


"zhj23" wrote:

Thanks for the prompt reply.

When I did exactly what you told: I call A1 as MyLastCell, and entered a
value of 5 (say), when I enter another value 8 in A2, I would expect my last
cell value to be 8. It doesnt. MyLastCell still equlas to 5.

May be I missed some of your points .... sorry.

zhj23

"JLatham" wrote:

The best way is to give the cell in the otherworkbook a name. Do this by
selecting the cell and then typing the name into the Name Box. The name box
is that area that will be displaying the address of the cell you've selected.
Click in the Name Box and give it a name such as myLastCell and end the
entry by pressing the [Enter] key - that's important.

Then in the other workbook, you can link to it as =Book1!myLastCell
and Excel will keep up with it no matter where it gets shoved around to.
For more information check out Excel Help on
Define named cell references or ranges
and
Name a cell or a range of cells


"zhj23" wrote:

Since the address of the last cell keeps changing as data are entered, how
can I link to this last cell from another workbook?

Thanks.

zhj23

Sorry. Perhaps I didnt make myself clear. What I wanted is the last non-empty
cell within a give range, c1:h10, say.

zhj23

"JLatham" wrote:

Nope, actually it sounds like you followed my instructions properly to a
successful conclusion. Unfortunately I gave you bad instructions. I
presumed you were inserting new information ahead of the last cell (either
new rows or columns) which would have kept pushing myLastCell over/down the
sheet and keeping it in the last position.

I'm not sure the following will assist you or not, depends on whether you
need to find the last cell in one worksheet in one particular workbook or
not. You could use this User Defined Function (UDF) to get a best guess at
that address. I say 'best guess' because UsedRange on a sheet is not always
accurate. It reports the last cell (in right-most column and largest
numbered row ever used) on the sheet and if you've deleted columns/rows, it
can be inaccurate.

Use [Alt]+[F11] to open the VB editor, then use the menu in it to Insert |
Module. Cut and paste the code below into it and modify it to refer to the
correct workbook and sheet in it. Then close the VB Editor.

Function FindLastForeignCellValue() As Variant
Dim tmpResult As String
On Error GoTo NoAnswer
tmpResult = _
Workbooks("OtherWorkbook.xls"). _
Worksheets("theWorksheet").UsedRange.Address
tmpResult = _
Right(tmpResult, Len(tmpResult) - _
InStr(tmpResult, ":"))
FindLastForeignCellValue = _
Workbooks("OtherWorkbook.xls"). _
Worksheets("theWorksheet").Range(tmpResult).Value
ExitFindLast:
Exit Function
NoAnswer:
FindLastForeignCellValue = ""
Resume ExitFindLast
End Function

to actually use this, put a formula like this into a cell:
=FindLastForeignCellValue()
It will probably change to all lowercase, that's normal.

You can also use it inside of a more complex formula such as
=A7 + 42 - FindLastForeignCellValue()

If you are interested in the last used cell in a particular row or column,
then it is a different ballgame.

Caveat: the function will only return a value when the other workbook is
also open.

And to get rid of that MyLastCell definition in your workbook, use Insert |
Name | Define and choose the name and hit the [Delete] button. It will be
removed as a definition.


"zhj23" wrote:

Thanks for the prompt reply.

When I did exactly what you told: I call A1 as MyLastCell, and entered a
value of 5 (say), when I enter another value 8 in A2, I would expect my last
cell value to be 8. It doesnt. MyLastCell still equlas to 5.

May be I missed some of your points .... sorry.

zhj23

"JLatham" wrote:

The best way is to give the cell in the otherworkbook a name. Do this by
selecting the cell and then typing the name into the Name Box. The name box
is that area that will be displaying the address of the cell you've selected.
Click in the Name Box and give it a name such as myLastCell and end the
entry by pressing the [Enter] key - that's important.

Then in the other workbook, you can link to it as =Book1!myLastCell
and Excel will keep up with it no matter where it gets shoved around to.
For more information check out Excel Help on
Define named cell references or ranges
and
Name a cell or a range of cells


"zhj23" wrote:

Since the address of the last cell keeps changing as data are entered, how
can I link to this last cell from another workbook?

Thanks.

zhj23

This should do that for you. Use it as I instructed before, and as before,
it only returns the value while the other workbook is open. If you are
expecting numeric values from that particular cell, you could change the code
in the error portion to return 0 instead of "" so that formulas will not be
too prone to display error messages such as #VALUE when the other book is not
open; although if you are dividing by this value then you could get a #Div/0
error while the other book is not open.

Function FindLastForeignCellValue() As Variant
'returns the value in column H in the largest
'numbered used row in that column
'Change "OtherWorkbook.xls" and
'"theWorksheet" as required.
'The other workbook must be open
'for a value to be returned
Application.Volatile
On Error GoTo NoAnswer
FindLastForeignCellValue = _
Workbooks("OtherWorkbook.xls"). _
Worksheets("theWorksheet"). _
Range("H" & Rows.Count).End(xlUp).Value
'use .CountLarge if in xl2007
ExitFindLast:
Exit Function
NoAnswer:
'can change to = 0 if working with numeric data
FindLastForeignCellValue = ""
Resume ExitFindLast
End Function


"zhj23" wrote:

Thank you very much for your patience.

I found the following codes in a book which is very close to what I want:

Function LASTINCOLUMN(rng As Range)
Dim LastCell As Range

Application.Volatile
Set LastCell = rng.Parent.Cells(Rows.Count, rng.Column) _
.End(xlUp)
LASTINCOLUMN = LastCell.Value
If IsEmpty(LastCell) Then LASTINCOLUMN = ""
If rng.Parent.Cells(Rows.Count, rng.Column) < "" Then _
LASTINCOLUMN = rng.Parent.Cells(Rows.Count, rng.Column)
End Function

Example: In a given range c1:h10, LastInColumn(c1:h10) will return the last
non-empty cell value in column C. If the program can be modified to give the
last non-empty cell value in column H, that will be fine with me.


zhj23

"JLatham" wrote:

Nope, actually it sounds like you followed my instructions properly to a
successful conclusion. Unfortunately I gave you bad instructions. I
presumed you were inserting new information ahead of the last cell (either
new rows or columns) which would have kept pushing myLastCell over/down the
sheet and keeping it in the last position.

I'm not sure the following will assist you or not, depends on whether you
need to find the last cell in one worksheet in one particular workbook or
not. You could use this User Defined Function (UDF) to get a best guess at
that address. I say 'best guess' because UsedRange on a sheet is not always
accurate. It reports the last cell (in right-most column and largest
numbered row ever used) on the sheet and if you've deleted columns/rows, it
can be inaccurate.

Use [Alt]+[F11] to open the VB editor, then use the menu in it to Insert |
Module. Cut and paste the code below into it and modify it to refer to the
correct workbook and sheet in it. Then close the VB Editor.

Function FindLastForeignCellValue() As Variant
Dim tmpResult As String
On Error GoTo NoAnswer
tmpResult = _
Workbooks("OtherWorkbook.xls"). _
Worksheets("theWorksheet").UsedRange.Address
tmpResult = _
Right(tmpResult, Len(tmpResult) - _
InStr(tmpResult, ":"))
FindLastForeignCellValue = _
Workbooks("OtherWorkbook.xls"). _
Worksheets("theWorksheet").Range(tmpResult).Value
ExitFindLast:
Exit Function
NoAnswer:
FindLastForeignCellValue = ""
Resume ExitFindLast
End Function

to actually use this, put a formula like this into a cell:
=FindLastForeignCellValue()
It will probably change to all lowercase, that's normal.

You can also use it inside of a more complex formula such as
=A7 + 42 - FindLastForeignCellValue()

If you are interested in the last used cell in a particular row or column,
then it is a different ballgame.

Caveat: the function will only return a value when the other workbook is
also open.

And to get rid of that MyLastCell definition in your workbook, use Insert |
Name | Define and choose the name and hit the [Delete] button. It will be
removed as a definition.


"zhj23" wrote:

Thanks for the prompt reply.

When I did exactly what you told: I call A1 as MyLastCell, and entered a
value of 5 (say), when I enter another value 8 in A2, I would expect my last
cell value to be 8. It doesnt. MyLastCell still equlas to 5.

May be I missed some of your points .... sorry.

zhj23

"JLatham" wrote:

The best way is to give the cell in the otherworkbook a name. Do this by
selecting the cell and then typing the name into the Name Box. The name box
is that area that will be displaying the address of the cell you've selected.
Click in the Name Box and give it a name such as myLastCell and end the
entry by pressing the [Enter] key - that's important.

Then in the other workbook, you can link to it as =Book1!myLastCell
and Excel will keep up with it no matter where it gets shoved around to.
For more information check out Excel Help on
Define named cell references or ranges
and
Name a cell or a range of cells


"zhj23" wrote:

Since the address of the last cell keeps changing as data are entered, how
can I link to this last cell from another workbook?

Thanks.

zhj23

Appreciate all the helps.

zhj23

"JLatham" wrote:

This should do that for you. Use it as I instructed before, and as before,
it only returns the value while the other workbook is open. If you are
expecting numeric values from that particular cell, you could change the code
in the error portion to return 0 instead of "" so that formulas will not be
too prone to display error messages such as #VALUE when the other book is not
open; although if you are dividing by this value then you could get a #Div/0
error while the other book is not open.

Function FindLastForeignCellValue() As Variant
'returns the value in column H in the largest
'numbered used row in that column
'Change "OtherWorkbook.xls" and
'"theWorksheet" as required.
'The other workbook must be open
'for a value to be returned
Application.Volatile
On Error GoTo NoAnswer
FindLastForeignCellValue = _
Workbooks("OtherWorkbook.xls"). _
Worksheets("theWorksheet"). _
Range("H" & Rows.Count).End(xlUp).Value
'use .CountLarge if in xl2007
ExitFindLast:
Exit Function
NoAnswer:
'can change to = 0 if working with numeric data
FindLastForeignCellValue = ""
Resume ExitFindLast
End Function


"zhj23" wrote:

Thank you very much for your patience.

I found the following codes in a book which is very close to what I want:

Function LASTINCOLUMN(rng As Range)
Dim LastCell As Range

Application.Volatile
Set LastCell = rng.Parent.Cells(Rows.Count, rng.Column) _
.End(xlUp)
LASTINCOLUMN = LastCell.Value
If IsEmpty(LastCell) Then LASTINCOLUMN = ""
If rng.Parent.Cells(Rows.Count, rng.Column) < "" Then _
LASTINCOLUMN = rng.Parent.Cells(Rows.Count, rng.Column)
End Function

Example: In a given range c1:h10, LastInColumn(c1:h10) will return the last
non-empty cell value in column C. If the program can be modified to give the
last non-empty cell value in column H, that will be fine with me.


zhj23

"JLatham" wrote:

Nope, actually it sounds like you followed my instructions properly to a
successful conclusion. Unfortunately I gave you bad instructions. I
presumed you were inserting new information ahead of the last cell (either
new rows or columns) which would have kept pushing myLastCell over/down the
sheet and keeping it in the last position.

I'm not sure the following will assist you or not, depends on whether you
need to find the last cell in one worksheet in one particular workbook or
not. You could use this User Defined Function (UDF) to get a best guess at
that address. I say 'best guess' because UsedRange on a sheet is not always
accurate. It reports the last cell (in right-most column and largest
numbered row ever used) on the sheet and if you've deleted columns/rows, it
can be inaccurate.

Use [Alt]+[F11] to open the VB editor, then use the menu in it to Insert |
Module. Cut and paste the code below into it and modify it to refer to the
correct workbook and sheet in it. Then close the VB Editor.

Function FindLastForeignCellValue() As Variant
Dim tmpResult As String
On Error GoTo NoAnswer
tmpResult = _
Workbooks("OtherWorkbook.xls"). _
Worksheets("theWorksheet").UsedRange.Address
tmpResult = _
Right(tmpResult, Len(tmpResult) - _
InStr(tmpResult, ":"))
FindLastForeignCellValue = _
Workbooks("OtherWorkbook.xls"). _
Worksheets("theWorksheet").Range(tmpResult).Value
ExitFindLast:
Exit Function
NoAnswer:
FindLastForeignCellValue = ""
Resume ExitFindLast
End Function

to actually use this, put a formula like this into a cell:
=FindLastForeignCellValue()
It will probably change to all lowercase, that's normal.

You can also use it inside of a more complex formula such as
=A7 + 42 - FindLastForeignCellValue()

If you are interested in the last used cell in a particular row or column,
then it is a different ballgame.

Caveat: the function will only return a value when the other workbook is
also open.

And to get rid of that MyLastCell definition in your workbook, use Insert |
Name | Define and choose the name and hit the [Delete] button. It will be
removed as a definition.


"zhj23" wrote:

Thanks for the prompt reply.

When I did exactly what you told: I call A1 as MyLastCell, and entered a
value of 5 (say), when I enter another value 8 in A2, I would expect my last
cell value to be 8. It doesnt. MyLastCell still equlas to 5.

May be I missed some of your points .... sorry.

zhj23

"JLatham" wrote:

The best way is to give the cell in the otherworkbook a name. Do this by
selecting the cell and then typing the name into the Name Box. The name box
is that area that will be displaying the address of the cell you've selected.
Click in the Name Box and give it a name such as myLastCell and end the
entry by pressing the [Enter] key - that's important.

Then in the other workbook, you can link to it as =Book1!myLastCell
and Excel will keep up with it no matter where it gets shoved around to.
For more information check out Excel Help on
Define named cell references or ranges
and
Name a cell or a range of cells


"zhj23" wrote:

Since the address of the last cell keeps changing as data are entered, how
can I link to this last cell from another workbook?

Thanks.

zhj23