Static Date Change based on Drop Down Selection

[snaplink22]

Hello,

I am creating a basic order tracking spreadsheet where I would like the date in one cell to change based off of the selection made in the drop down menu of another cell. I don't want the date to update automatically though and have it remain static. The trick is that the date is not just today's date, but the date the status was changed minus a number of days, here's an example:

A1 - Drop down menu
B1 - Equals the date the cell A1 was changed, plus ten days

I'm sure it's a simple formula that I'm missing. Here's what I've come up with so far =today()+10. This takes the current date and adds ten days, but it keeps updating the date after every day has ended.

 

[mumps]

Try the macro approach. Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in cell A1.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    Target.Offset(0, 1) = Date + 10
End Sub

 

[snaplink22]

Mumps,

Not sure if that macro is missing the B1 section though. I inputted the macro, but nothing happens when I update the drop down menu. Shouldn't there be something in there that adds the date, plus 10, to cell B1?

 

[mumps]

Target.Offset(0, 1) refers to B1. Make sure you have placed the macro in the worksheet code module (not a regular module) as I described in Post #2 and that the drop down list is in cell A1.

 

[snaplink22]

That got it, but now I see that's not exactly what I'm looking for. What I want to accomplish is to change the date based on what is selected in the drop down menu. For example: In cell A1, if "order" is selected I want the date when it was changed plus 10 days, but if "in process" was selected I want the date when it was changed plus 8 days. I have about 15 different selections in the cell drop down that would need to apply to this rule.

 

[mumps]

Here is the macro with the 2 selections mentioned. Just follow the pattern and add the remaining "Cases".

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    Select Case Target.Value
        Case "order"
            Target.Offset(0, 1) = Date + 10
        Case "in process"
            Target.Offset(0, 1) = Date + 8
    End Select
End Sub

 

[Smitty]

Welcome to the Board!

With that many selections, I'd use a lookup table and reference it in your code. This assumes you have a table on a sheet named List:

Private Sub Worksheet_Change(ByVal Target As Range)
'   Code goes in the Worksheet specific module
    Dim rng As Range
    Dim x As Long
    Dim lstObj As ListObject
    '   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
        Set rng = Target.Parent.Range("A1")
        Set lstObj = Sheets("List").ListObjects(1)
        '   Only look at single cell changes
            If Target.Count > 1 Then Exit Sub
        '   Only look at that range
            If Intersect(Target, rng) Is Nothing Then Exit Sub
        '   Action if Condition(s) are met (do your thing here...)
            x = Application.Match(Target.Value, lstObj.ListColumns(1).DataBodyRange)
            Target.Offset(, 1).Value = lstObj.ListColumns(2).DataBodyRange.Cells(x)
End Sub

 

[snaplink22]

That did it, however, how do I apply that macro to the rest of the rows in column A? I have about 200 rows of items to track.

 

[Smitty]

That did it, however, how do I apply that macro to the rest of the rows in column A? I have about 200 rows of items to track.

Change the target range to suit: Set rng = Target.Parent.Range("A1")

 

[snaplink22]

Sorry, never worked with macros before. Is this what I want to type in:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Set rng=Target.Parent.Range("A1")) Is Nothing Then Exit Sub