Month of Most Current Data

[LadyDi]

I have a quick question for you. I have a table in Excel that shows the order volume for my department for each month this year. This table also shows historical data from three previous years. I have created a series of tables that will show year to date data for all years (i.e. it shows January through April data for 06, 07, 08, and 09). I would like to set it up so that the table title will display the month with the most recent data. In other words, it will show April until I enter May's data and then it will show May. Is there anyway that I can accomplish this? The months are the columns and the years are the rows in this table. Any assistance you can provide would be appreciated.

 

[HaHoBe]

Hi, LadyDi,

what about this formula?

="up to month: " & INDEX(A:A,MATCH("",A:A,-1))

Or do you prefer a UserDefined Function or the change based on an event like the entry of a new month?

Ciao,
Holger

 

[LadyDi]

I would prefer the change based on an event (the addition of another month). Is that possible?

 

[HaHoBe]

Hi, LadyDi,

code goes behind the worksheet where the event should be fired (only by entering values into the worksheet):

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Columns(1)) Is Nothing Then
  Range("B1").Value = Cells(Cells(Rows.Count, "A").End(xlUp).Row, "A").Value
End If
End Sub

Ciao,
Holger