Data Validation based on List and value in other cells

[STEVEMILLS04]

Thank you in advance for any help provided!

I have a column that has a data validation list which is =Potential. I need to change it to a formula that will change if a particular set of events occurs.

Cell J is the first cell the user changes, the should initially be able to select from the Potential list. Then, once 7 other columns are filled in based on a list, cell AK changes to complete. All 7 columns have a score depending on the data selected. If the score does not meetsa minimum requirement, cell AJ changes to Change. Only when cell J is originally seleted as Promotable and when cell AK is complete and cell AJ is Change, cell J should then be changed to "Well Placed". Otherwise, cell J will stay as the user original selected from the drop down list.

If Cell AK is equal to incomplete, the user should be able to select from the Potential data list. If cells AK = Complete, AJ = Change and the user originally selected Promotable in cell J, cell J should change to "Well Placed".

This is the formula I have in the data validation but it tells me there is an error. Still lets me select OK but does not give the desired results.

=IF(AK:AK="INCOMPLETE",Potential,IF(AND(AK:AK="COMPLETE",AJ:AJ="CHANGE",J:J="Promotable"),"Well Placed",Potential))

 

[Andrew Poulsom]

Why are you using entire columns in your formula? Note that a cell can contain Data Validation or a formula, but not both. Changing the value of a cell that contains Data Validation would require some VBA code in the Worksheet_Change or Worksheet_Calculate event procedure.

 

[STEVEMILLS04]

Andrew,

This was the 100th formula I tried. I originally had $AK3, etc... but it kept giving me an error so I figured I would keep trying other things.

Would you be able to assist me with the VBA? I do not know much about it.

Thank you again.

 

[Andrew Poulsom]

In which rows in column J do you have Data Validation?

 

[STEVEMILLS04]

Currently Rows 3 through 378

 

[Andrew Poulsom]

Sorry, another question. In which 7 columns is the user entering data?

 

[STEVEMILLS04]

Sorry, another question. In which 7 columns is the user entering data?

Column M, then S-X. All 7 are based on data validation lists

 

[Andrew Poulsom]

What formulas do you have in AJ3 and AK3?

 

[STEVEMILLS04]

What formulas do you have in AJ3 and AK3?

AJ3 IS: =IF(AND(M3<>"",S3<>"",T3<>"",U3<>"",V3<>"",W3<>"",X3<>"",AI3>18),"OK","CHANGE")
AK3 IS: =IF(OR(M3="",S3="",T3="",U3="",V3="",W3="",X3=""),"INCOMPLETE","COMPLETE")

These would be copied down through rows 378

 

[Andrew Poulsom]

Try this in the module for the worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Cell As Range
    If Application.Intersect(Target, Range("M3:M378,S3:X378")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    For Each Cell In Target
        If Range("J" & Target.Row).Value = "Promotable" And Range("AJ" & Target.Row).Value = "CHANGE" And Range("AK" & Target.Row).Value = "COMPLETE" Then
            Range("J" & Target.Row).Value = "Well Placed"
        End If
    Next Cell
    Application.EnableEvents = True
End Sub