Excel Formula Help - Data Validation with Custom Rules

[Fr0dders]

Hi All

wondering if there was any excel wizards out there that can help me with this. I want to restrict entry into Column B which is going to be a cell that starts out Empty, but will be filled in with COMPLETE when somebody has done something with other Cells

Basically I want to test that if C2 = a value, then D2 must not be empty.

I could get conditional formatting using an AND IF statement to check this and apply a green / red colour shade, but I'm wanting it to prevent data being entered in the cell.

Only problem is, I want this to apply down each row. So that you can't enter into B3 if C3 is true and D3 is empty, likewise D4 if C4 is true and D4 is empty etc..

From what I've seen I'd have to apply a separate data validation formula to each cell to achieve this is that true ?

 

[wesimmo]

I'm not sure if I've got what you're asking, but..

Can you not put your IF / AND function in column B and then lock the cells to prevent anything being typed over?

 

[Fr0dders]

The goal here is that I have a spreadsheet of requests

There's a column with the type of request. When that cell matches a certain type of request I want to prevent people from entering anything into the completed column until they have filled out column D which needs to be read by the person who made the request.

This will hopefully prompt them to remember to fill in the data in that column so they can then mark it as complete.

 

[wesimmo]

You could use a helper cell.

=IF(LEN(D2)=0,"","Complete")

Put that in column A or something.

Then in data validation for B2 use the list option and then Source = A2.

That way they can select "Complete" from the drop down if D2 has something in it, if not you can only select <blank>.

edit: obviously you can change the helper cell to make the conditions more specific or sophisticated.

 

[dazzerd]

This can only be done in code I think. You can use the worksheet change event to capture each change. Check if the change is relevant to be tested if so you can then "conditionally" lock or unlock the cell in question.

It wont be straightforward though as locking only works if the sheet is protected so you will have to deal with protecting and unprotecting the sheet. As well as only triggering the code when certai cells or ranges change.

You could also do this using a sophisticated UDF as well but you will still need to write code.

Sounds to me you are getting into the realms of needing a form or something similar.