Excel 2003 - Dynamic named ranges

[bcr123]

I am making mistake and I can’t understand what I am doing wrong.
Dynamic named ranges is what I was busy with in Excel 2003.

This is practical problem:

In worksheet `A` I have data in column O.

I need last value (and last value will change) from column O to display in another worksheet (`B`) in cell A634.

This is what I did in for column O in `A`:

Insert>Name>Def ine

I’ve typed “range” in Name in workbook, while I’ve typed into Refers to the following:

=OFFSET($O$4,0, 0,COUNTA($O$4:$ O610),1)

As of now last value in O column is in O599, with column work area being extended down to O610 and before O610 is reached I will be expanding column by adding extra 10 rows. (Insert>Row)

If this is correct and if this means that range in O will extend as I add more rows with new data – What I need to type in cell A634 in another workbook in order to have last value from O column displayed? (after each addition of new data and rows and calculations in O)

Thank you in advance.

[TheSmileyCoder]

Well this is what I could come up with.
Add a module, and add this code to the module

Public Function LastValue(strCol As String)
    
    Dim intI As Integer
    intI = 1
    Do While Sheet1.Range(strCol & intI + 1) & "" <> ""
        intI = intI + 1
    Loop
    LastValue = Sheet1.Range(strCol & intI)
    Debug.Print LastValue
End Function

This function will run through all the values in the column strCol given as input parameter, until it reaches a empty cell, at which point it will return the value of hte previous cell.


In your worksheet, in cell A634 put in the function:
LastValue("O") where "O" is your column name.

In worksheet A, add code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Sheet1.Range("A" & 634).Dirty
End Sub

Making the cell re-evaluate whenver you change something. You could probably limit it to only evaluate if the Target is an item in column O.

[bcr123]

Thank you very much TheSmileyOne.

Will try to work it out.

Appreciated.