IF statement conflicting with Validation Settings

[DPChristman]

I have a shared spreadsheet that I am trying to eliminate laziness in on the part of my users.

Column B contains a case number, and column S contains a position (validated lookup of 10 options including Other and blank)

My issue is that users are failing to fill in the position of the person on the case line.

It occurred to me that the simplest solution is an IF statement.

Basically, IF column B (example, row 1) has a number in it, the position field will show the word Other, and be highlighted until it is filled in with one of the other accepted values.

So the formula would be something like if( B1>0,"Other","" ) or if( B1>"","","Other" )

It seems pretty simple, but every time I try to create it, I get this pop-up message the value you entered is not valid. a user has restricted values that can be entered into this cell". I have cleared the validation settings and reset them to allow for all 10 possible positions listed, and I sill get this message.

What am I missing?

Ideally, I would like to prevent the spreadsheet from being saved of the position cell is not filled out, but I am not sure how to do that. The spreadsheet will always have hundreds of unused lines for case entries that will have that cell empty.

Any Ideas?

 

[jmacleary]

Hi there. If its OK to use macros, you could have a worksheet change event that if a column B cell is changed sets the same row column S to the value "Other". Add conditional formatting to the cell for highlighting. You could also have a workbook beforesave routine that looked down column S for any "Other" values and a msgbox telling the user to complete it/them.