Data validation, dropdown and formula

[Peltz]

Hello there.

Im sorry if this is a really green question, but unfortunately I'm quite new to Excel.

I'm making a tool to monitor different work processes: What I have done so far is:

I have made a list ("Status") and use the lists entries for data validation on cell C23. The entries in the list is "Yes", "No", "Unknown",...The reason I have chosen this i primarily because of the drop down function, which i want to have.

In F23 have a cell for date entry.

What I want to do is the following:

If todays date => the date entered in F23, C23 is changing its value to "Unknown". To change the "Status" the user is forced to put in a date > today date.

First i tried to control C23 from different functions in another cell. As far as I have learned, this cant be done. Im figuring that i need to have a function in the source of the list, in C23, or doing some VBA magic.

I've search around and cannot find a solution to this. Any help would be greatly appreciated

BTW, I'm working on excel 2003.

Regards
Excel Noob

 

[ogo]

Hi Peltz welcome to the forum,

Please try the code below,

Private Sub Worksheet_change(ByVal Target As Range)
 
x = Date
If Target.Column = 6 Or Target.Column = 3 Then
 If Cells(23, 6).Value <= Date Then
    Cells(23, 3).Value = "Unknown"
 End If
End If

End Sub

 

[jbeaucaire]

If this is a one-use form, VBA is fine, I suppose. But if you will reopen the form in later days and make possible edits on your form anywhere, if the current date has now moved past the date in F23 (which was in the future when entered, but is now in the past), the code suggested above would revert your C23 value back to "unknown".

Is this what you want?

If you want the Status in C23 to remain fixed once you've entered it, you'll need to adjust the DV list with a custom formula.

I would suggest you create two named ranges, one called List1 that has all three of your options Yes/No/Unknown, and one called List2 that just has the option Unknown.

Then in your DV settings for C23, use:

Allow: List
Source: =IF($F$23>=TODAY(), List1, List2)


Now, if the date in F23 is less than today's date, the only option you will be given in the drop down is "unknown". It's not an "automatic" change to the displayed value like VBA could do, but it is still restrictive, and once a choice is made, it wouldn't get changed later when the dates have changed.

 

[Peltz]

First of all. Thank you you guys for replies, I'am so gratefull.


Private Sub Worksheet_change(ByVal Target As Range) x = DateIf Target.Column = 6 Or Target.Column = 3 Then If Cells(23, 6).Value <= Date Then Cells(23, 3).Value = "Unknown" End IfEnd IfEnd Sub</PRE>This worked perfectly!!

If this is a one-use form, VBA is fine, I suppose. But if you will reopen the form in later days and make possible edits on your form anywhere, if the current date has now moved past the date in F23 (which was in the future when entered, but is now in the past), the code suggested above would revert your C23 value back to "unknown".

Is this what you want?

That was just what I wanted. The task in mind is a repetitive task which have deadlines. Now every time the date expires the status is "Unknown" since nobody put a new expiery date, and that prompts users to set a new date.

Since i have several such operations in the form, ill try to figure out an effective way to repeat the operation.

I think i sort off know whats going on in the code except for the two first lines:

x = Date
If Target.Column = 6 Or Target.Column = 3 Then


Could the code be written in a way that this operation could work on multiple pairs of cells, instead of my primitive take which would be to repeat the code (and change the values) 20 times...

Ill chew on that tonight.

Again, thank you so much for your replies

Regards

 

[jbeaucaire]

Let's assume the "watch dates" are in F2:F100, and the status of C2:C100 needs to change to "unknown" for every date in the past.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell as Range

Application.ScreenUpdating=False
Application.EnableEvents=False

For Each Cell in Range("F2:F100")
    If cell.Value > 0
        If Cell.Value < Date Then 
            If Range("C" & cell.row) <> "unknown" Then Range("C" & cell.row) = "unknown"
        End If
    End If
Next Cell

Application.ScreenUpdating=True
Application.EnableEvents=True

End Sub

 

[Peltz]

Thanks

I did it the dummy way. That is untill my VPN got disconnected, and work server was down due to maintanence... so it seems all over again

I clearly see that I have A LOT to learn, this is a whole new and fun experience... there seems to be few limits to what Excel can do. Soon I'll have Excel making coffee for me.

Im afraid the changing cells dont fall inn quite that neat.

The "system" is like this (pairwise):

Changing cell /"watch cell"
C23 F23
C24 F24
C25 F25

(+4 collumns)

G23 J23
G24 J24
G25 J25

9 times. (That is 9 groupes).

BTW: Out of curiosity, is it possible to register dates when changes in cells are being made? For example, when people select "Yes" in the list?

Regards

 

[jbeaucaire]

You can expland the watch range to include all the groups, like so...

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range

Application.ScreenUpdating = False
Application.EnableEvents = False

For Each Cell In Range("F2:F100,J2:J100,N2:N100,R2:R100,V2:V100,AA2:AA100,AE2:AE100,AI2:AI100,AM2:AM100")
    If Cell.Value > 0 Then
        If Cell.Value < Date Then
            If Cell.Offset(, -3) <> "unknown" Then Cell.Offset(, -3) = "unknown"
        End If
    End If
Next Cell

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

The timestamps could be added in a similar way.