Formula for Date ranges

Hello Stephen,

I typically do not give away this stuff free, especially to a business, but the following example will be available for download until next Monday, when I remove it.

(broken link)

Kevin

Using Kevin's sample data, here's another way with VBA. The slight advantage is that the worksheet formulas per Kevin's example can be elimimated and therefore do not have to be filled down as new data is entered in columns A & B. Also, the first row with the numbers 1 to 12 can be eliminated.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim col As Integer
With Target
If .Column = 1 Or .Column = 2 Then
Range(Cells(.Row, 3), Cells(.Row, 15)).ClearContents
col = Month(Cells(.Row, 1)) + 2
Cells(.Row, col).Value = Cells(.Row, 2).Value
End If
End With
End Sub

Hi Stephen

Here is some code that should be placed with the Module of the Worsheet concerned. The code will run automatically each time a date is entered in the range A2:A5000.

To get to the Worsheet Module, right click on the Sheet name tab and select "View Code" then paste in the code. Then Push Alt+Q to return to Excel.

This code is set up for your date headings being in C1:N1 eg Jan Feb Mar etc.
The date to check being anywhere in A2:A5000.
The dollar amount being in B2:B5000.

Private Sub Worksheet_Change(ByVal Target As Range)
'Written by OzGrid Business Applications
'www.ozgrid.com
Dim rWatchRange As Range
Dim rDateRow As Range
Dim sMonth As String
Dim iEntryRow As Integer
Dim dDollar As Double

If Target.Cells.Count > 1 Then Exit Sub

Set rWatchRange = Range("A2:A5000")
If Intersect(Target, rWatchRange) Is Nothing Then Exit Sub

'Entry cell contains a Date and the cell to the right
'has a dollar amount.
If IsDate(Target) And IsNumeric(Target.Offset(0, 1)) Then
'Entry cell falls with the date 12/22/2000 and 22/2/2001
If Target.Value > DateSerial(2000, 12, 22) And _
Target.Value < DateSerial(2001, 2, 22) Then

'Parse text Month to String
sMonth = Format _
(DateSerial(Year(Target), _
Month(Target), Day(Target)), "mmm")
'Parse Month heading range to Range Variable
Set rDateRow = Range("C1:N1") 'Jan to Dec
'Parse Row number to Integer
iEntryRow = Target.Row
'Parse the amount to Double
dDollar = Target.Offset(0, 1).Value
'Find the date heading and offset one less than
'the entry cells row.
rDateRow.Find _
(What:=sMonth, After:=rDateRow.Cells _
(1, 1)).Offset(iEntryRow - 1, 0) = dDollar

Set rDateRow = Nothing
Set rWatchRange = Nothing

End If
End If
End Sub

This could also be set up to be run whenever a button is clicked. Then an InputBox could collect the date range to check and move all data the meets the criteria.

Dave

OzGrid Business Applications

I think it advisable also to provide for the possibilities of :
- an amount in column B being changed after the date has been entered in column A, and
- a date in column A being changed from one month to another month ?

Thanks to everyone for their help. I will try your suggestions.