If one cell changes, then change the vale of other cells

[The Idea Dude]

Row 4, colC to ColJ

the have numbers in them ranging from .5 to 36 (they represent months).

The 15 rows benath row 4 contain dates in them.

How do i make it so that if a value in row 4 changes then the dates in the relevant column and in rows 5-19 adjust.

EG Row 4 ColD has 12 in it
ColD in row 5, 6, 7 and 8 respectively have the dates 12/12/05, 13/12/05, 24/12/05, 2/1/06.

If I change D4 to 24 then all the dates above would increase 12 months. ie 12/12/06, 13/12/06, 24/12/06, 2/1/07.

If i entered 3 in D4 then all dates above would decrease 8 months (or show 8 months earlier). ie 12/4/05, 13/4/05, 24/4/05, 2/5/05.

Thanks

 

[nbrcrunch]

not sure, but it sounds as if you are asking how to write dates with formulas

http://www.cpearson.com/excel/topic.htm

 

[KJLoomis]

It's a little messy, but if you paste this macro to the Micorsoft Excel Objects - Sheet for the sheet in question. This macro will automatically run and make adjustments to the dates in the 4 columns (Col 5,6,7,&8) next to column 4, when a new value is entered in column 4. Not my best work to date, but it should give you exactly what you are looking for. Test it extensively just to make sure.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

If Target.Column = 4 And Target <> "" Then
If IsNumeric(Target.Value) = False Then
Application.Undo
Application.EnableEvents = True
Exit Sub
End If
NewVal = Target.Value
Application.Undo
For Each Cell In Range(Cells(Target.Row, 5), Cells(Target.Row, 8)).Cells
If Cell.Value <> "" Then Cell.Value = DateAdd("m", NewVal - Target.Value, Cell.Value)
Next Cell
Target.Value = NewVal
End If

Application.EnableEvents = True

End Sub