As Tim said, it would help to know what's going on in Columns D-F. This
problem is solved better with a macro. Suppose these columns have the most
recent dates, with the most recent date in Column D. Do the following:
1. Make sure the Control Toolbox toolbar is visible by clicking the
View>Toolbars menu items in Excel.
2. Click the Command Button icon on this toolbar to create a command button.
3. Right click the new button and select Properties, which will bring up the
properties window. Change the Name property to "cmdUpdate" and the Caption
property to "Update". If you want, you can change the appearance, such as
font, using the other properties.
4. Right the button again and select View Code. This puts you in the VBA
editor, where you'll see
Private Sub cmdUpdate_Click()
End Sub
5. Copy the following code into this procedure
Private Sub cmdUpdate_Click()
Const iDATE_COLUMN_OFFSET As Integer = 2
Const iNUMBER_DATE_COLUMNS As Integer = 3
Const iNEWVALS_COLUMN_OFFSET As Integer = 6
Const strHOMECELL As String = "B1"
Dim i As Integer
Dim j As Integer
Dim iNew_count As Integer
Dim iSerial_num As Long
Dim rngHome As Range
Set rngHome = ThisWorkbook.ActiveSheet.Range(strHOMECELL)
With rngHome
' Determine the number of update items
iNew_count = 0
Do Until .Offset(iNew_count, iNEWVALS_COLUMN_OFFSET).Value = ""
iNew_count = iNew_count + 1
Loop
' Now check each serial number to see if needs an update
iSerial_num = 0
Do Until .Offset(iSerial_num, 0).Value = ""
' Compare this serial number against those in the update list
For i = 0 To iNew_count - 1
If .Offset(i, iNEWVALS_COLUMN_OFFSET).Value = _
.Offset(iSerial_num, 0).Value Then
' Found a match
GoSub UpdateDates
Exit For
End If
Next i
iSerial_num = iSerial_num + 1
Loop
End With
Exit Sub
UpdateDates:
With rngHome
' Move the old date values one column over
For j = iNUMBER_DATE_COLUMNS - 1 To 1 Step -1
.Offset(iSerial_num, iDATE_COLUMN_OFFSET + j).Value = _
.Offset(iSerial_num, iDATE_COLUMN_OFFSET + j - 1).Value
Next j
' Put the new date in the first of the date columns
.Offset(iSerial_num, iDATE_COLUMN_OFFSET).Value = _
.Offset(i, iNEWVALS_COLUMN_OFFSET + 1).Value
End With
Return
End Sub
6. Return to the spreadsheet and click the Exit Design Mode button on the
Control Toolbox toolbar to make the button active. To update the list, just
click the Update button you have created.
Note that the actual updating all occurs in the subroutine UpdateDates. I've
written to update based on my supposition for the function of columns D-F.
Change the subroutine to accomplish whatever else you might want to do.
I hope this helps. Good luck.
