Data Validation Help

S

Sean

I am trying to not allow any values to be typed in a row if the last column
in that row has the word "COMPLETED" in it. the word completed is linked to
another sheet but it does display the exact word on this sheet with a =cell
formula between sheets.
 
M

Mladen_Dj

I am trying to not allow any values to be typed in a row if the last column
in that row has the word "COMPLETED" in it. the word completed is linked to
another sheet but it does display the exact word on this sheet with a =cell
formula between sheets.
Click to expand...

Let's say last column is column E. Select range you want to validate
and in Data Validation dialog under Costom enter this formula:

=IF($E1="COMPLETED";ISBLANK(A1);NOT(ISBLANK(A1)))

It will not alow user to enter text or value in cell after "COMPLETED"
is in E1, but it still will be posible to delete values or text from
cells.
 
S

Sean

That didn't really work, all I did was change the E1 to my cell with the
completed in it. What was the A1 referring to also.
 
M

Mladen_Dj

That didn't really work, all I did was change the E1 to my cell with the
completed in it. What was the A1 referring to also.
Click to expand...

Sean,
Its important to have dollar sign ($) in reference E1. Instead E1 put
upper right cell of your validated range, including column with
"COMPLETED" formula. Also, instead A1 you need to put inside uper left
cell of your validation range.

regards,
 
Top