Need Help, Validate a cell vs a table

[bpinter]

Hi,

I will start out by saying… I’m using a live document, so I can’t use Macro/VBA.

I want to use data validation to check another cells text against a table on another sheet. If the text matches against text in the table, it should not allow data to be entered into the cell. The issues that Im having is the cells I’m comparing against are in a table themselves, so they need to be able to check against the text on the appropriate row.  

Is this possible to do?

[#2]

@bpinter  A sample would help and more details on what is allowed or not allowed would help.  You say it can't be in the table but then later you say it has to check that row...

Let's say you don't want any duplicate entries on any given row and the data is in the range B2:E7.  Then select that range and in Data Validation select 'custom' and enter this formula:

=(COUNTIF($B2:$E2,B2)=1)

If instead you want unique values entered in the entire table then use:

=(COUNTIF($B$2:$E$7,B2)=1)

[#3]

@mtarler 

Please see picture. Hopefully this better explains.

[#4 · Accepted solution]

@bpinter  I think this is what you want:

=COUNTIF(Sheet2!$A$2:$A$3,$B2)

where Sheet2!$A$2:$A$3 is the range of the acceptable values and $B2 is the top row of the Task column.

[#5]

@mtarler 

This work great! Thank you!

Is there a way to have it error if the cell is blank also? 

[#6]

well, I would say just uncheck the box in data validation that says "Ignore blank" but there appears to be a bug there. AND you should also know data validation only ACTS when the cell value is changed. So if you have a value already in the cell that doesn't meet the data validation (e.g. you added/changed the validation criteria or changed the values that the data validation is looking at to check if it is valid) then it will not send an alert even if you click on the cell and hit enter (i.e. without changing the value). But if you click on the cell and edit the value or even hit F2 to go into edit mode and then hit Enter it will give the error message BUT if you then hit Cancel or 'x' out of the warning it will return to the PREVIOUS value which was not technically valid. I believe this is all 'as designed' and limitations that must be accepted (as the alternatives could wreak havoc).
The 'bug' I mentioned is that by unchecking "Ignore blank" it appears to only 'half' work. IF you have a valid value in the cell and then clear the cell it appears to ignore the blank even with the checkbox unchecked. If you try to enter a wrong value it will give the alert (as expected) and then if you try to follow that up with a blank value then it will continue to give the alert (i.e. the unchecked ignore blank option works in this case).