Protecting/restricting an excel spreadsheet - Can I prevent typing in a cell with a drop down list?

Question

I am working on a spreadsheet for data entry and I'm gradually restricting it to prevent mistakes/omissions and general fiddling by factory floor staff. I've muddled my way through most of my problems but I've hit a wall.

I have several drop down lists and want to prevent typing in the cell containing the drop down lists (even if it matches a list item).

This is because the validation is set up so the list is unavailable unless data in the previous cell has been entered.

I've used the following formula for the data validation in column G for example:

=IF(NOT(ISBLANK($F54)),INDIRECT("FaultReason[Fault/Reason]"))

This works OK, the list is inaccessible if the cell in column F is blank, but unfortunately its still possible to type a value, which if its on the list will be accepted, even if the previous cell is blank.

Is there a way to prevent that? or a completely different way to achieve what I'm looking for?

I would prefer if instead of making the list inaccessible it could come up with an error message when trying to select a list item with nothing in the preceding cell similar to the validation I set elsewhere but I cant see how I could do that. Not the end of the world though.

Answer 1

Hi hope you still couldn't figure out the solution and am trying to share my knowledge here...

I also encountered with such similar situation where my associates manually type data instead of selecting from the list.

I managed to restrict the entry if the adjacent cell is blank or unfilled.

My scenario is in column N, O & P.

N carries monetary value. O caries on whose behalf (By the Company / Against the Company) P carries the Remarks (dependent list of Column O)

Refer the picture for clarification.

Formula I used is,

1. check if Column P is filled, accept the value in column N.
2. check if column O is filled, accept the value in column P. that's it. simple.

Formula for column N: =(IF(OR(P5="FIR",P5="Criminal"),N5=0,ISNUMBER(N5)))*AND(LEN(P5)<>0)

Formula for column O: create List.

Formula for column P: create Dependent List of column O. ENSURE TO UN-CHECK THE IGNORE BLANKS button.

By this way, i managed to restrict the filling of data with adjacent cells empty. However, I'm still to figure out to restrict typing.

enter image description here