Multiple Criteria Data Validation

[Spartacus9]

Hi,

I'd appreciate help with this problem. I'm trying to restrict entry in a cell to a date or, if specific text is entered in the adjacent cell, allow the user to type permitted text.

The cell I'm restricting is a project deadline date. So if the user selects "on hold" in the satus cell (adjacent) I want to permit them to type "TBD".

I was hoping for a solution without using VBA if possible.

Thanks

 

[dermie_72]

I believe that you can have text, OR date but you can't have both using data validation.

Would it be easier to just leave the cell blank, or use an end of project date to signify the delay?

 

[skywriter]

Here's a way to do it with data validation, but it has many flaws.
So if you select cell A1 in this case and go to data validation, choose List under allow, don't click the box next to In-cell dropdown.
In the source box I have the following:
=IF(B1="on hold",C1)
If B1 has on hold in it and you try to type anything except TBD in cell A1 you will get a box saying the value is invalid. You can use your own custom message.
The issues with this are that if on hold is not in B1 you can put any value in A1, it is not limited to a date. Another problem with data validation is someone can copy a blank cell on your worksheet and paste it into A1 and the data validation is gone. If there is a value besides TBD already in cell A1 changing B1 to on hold doesn't have any effect, you would have to try to change the value already there before you would get a message.

Excel 2010

	A	B	C
TBD	on hold	TBD

<tbody>
[TD="align: center"]1[/TD]

</tbody>

Sheet1
​