By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,654 Members | 1,816 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,654 IT Pros & Developers. It's quick & easy.

Excel 2003 - Dynamic named ranges

P: 47
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>Define

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.
Feb 17 '10 #1
Share this Question
Share on Google+
2 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
Well this is what I could come up with.
Add a module, and add this code to the module
Expand|Select|Wrap|Line Numbers
  1. Public Function LastValue(strCol As String)
  2.  
  3.     Dim intI As Integer
  4.     intI = 1
  5.     Do While Sheet1.Range(strCol & intI + 1) & "" <> ""
  6.         intI = intI + 1
  7.     Loop
  8.     LastValue = Sheet1.Range(strCol & intI)
  9.     Debug.Print LastValue
  10. 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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.     Sheet1.Range("A" & 634).Dirty
  3. 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.
Feb 18 '10 #2

P: 47
Thank you very much TheSmileyOne.

Will try to work it out.

Appreciated.
Feb 19 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.