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))

 

[STEVEMILLS04]

Andrew,

This worked great! Thank you so much!

Is there a quick and easy way to prevent the user from changing J if the VBA changes it to Well Placed?