cell cannot be blank if a certain value of another cell exists

[valkie]

hi,
I want enforce users to enter a value to column E of Sheet2 if the correspondent row of column B on Sheet1 has a certain value, say "PRESENT".

So let's say, cell Sheet1.B1 = "PRESENT", then when the users reach to Sheet2.E1, they cannot skip it unless they enter something.
But if Sheet1.B1 = "" or a different value, then users can tab pass Sheet2.E1 without problem.

Any help is really appreciated!
Valkie

[arlu1201]

Is this validation just for 1 cell? Or are there chances of many rows having the similar type of validation?

[Fotis1991]

Hi and welcome to the Forum.

One way to do this, is using the Custom Validation.

In my case i can give you the solution that you need, using one helper cell. Let's say IV1, in sheet 2.

So in IV1, put the formula. =IF(Tag<>"present";1;0)+IF(AND(Tag="present";E1<>"");1;0) Tag is the Name that a i gave to Sheet1!B1.

Now in the range that you need to "Lock", use this formula. =COUNTIF($IV$1;1)

dATA>>vALIDATION>Custom>>..... (In my example, A1:P30>>YELLOW AREA)

Hope to helps you.

[valkie]

Is this validation just for 1 cell? Or are there chances of many rows having the similar type of validation?

Hi Arlu,
This validation is for the whole Sheet2!E column, so every row in E column will need this validation.
So let's say
Sheet2!E1 will be validated based on the value of Sheet1!B1
Sheet2!E2 will be validated based on the value of Sheet1!B2

Thanks!

[valkie]

hi Fortis,
Somehow the spreadsheet does not work for me.
It seems like the spreadsheet works differently, it prompts me error if I enter a value to Sheet2!E1 when Sheet1!B1 is empty OR having any value different than 'Present'.
But if Sheet1!B1 has value as 'Present', then it does not prompt me any error whether I enter a value to Sheet2!E1 or not.

Thank you for your help!
Valkie

Hi and welcome to the Forum.

One way to do this, is using the Custom Validation.

In my case i can give you the solution that you need, using one helper cell. Let's say IV1, in sheet 2.

So in IV1, put the formula. =IF(Tag<>"present";1;0)+IF(AND(Tag="present";E1<>"");1;0) Tag is the Name that a i gave to Sheet1!B1.

Now in the range that you need to "Lock", use this formula. =COUNTIF($IV$1;1)

dATA>>vALIDATION>Custom>>..... (In my example, A1:P30>>YELLOW AREA)

Hope to helps you.

[Fotis1991]

Hi valkie!

Are you sure that you checked my attachement well??

....it prompts me error if I enter a value to Sheet2!E1 when Sheet1!B1 is empty OR having any value different than 'Present'.

in my example Sheet1 & Sheet 2. In sHEET 1, b1=empty. In Sheet2>>E1, i put any value. No error. In the range that i have lock, i can put any value, because Sheet1!BI>>>IS NOT "PRESENT"!!

2ND & 2ND Sheets: Sheet1!b1=something else(not "present". In Sheet2!e1, put any value you like.In the range that i have lock, i can put any value, because Sheet1!BI>>>IS NOT "PRESENT"!!
Post4

....But if Sheet1!B1 has value as 'Present', then it does not prompt me any error whether I enter a value to Sheet2!E1 or not.

In this case if Sheet1!b1="present" and Sheet2!e1=EMPTY, then you can put no value in range A1:P30.

Pls let me know!

[valkie]

Ah.. I think I got your idea now.
"In this case if Sheet1!b1="present" and Sheet2!e1=EMPTY, then you can put no value in range A1:P30."
But what I need is IF Sheet1!B1="present" then users can enter any value (or leave blank) any other cells except for Sheet2!E1, they HAVE TO enter something into Sheet2!E1.

Thank you, Fortis.
Valkie

Hi valkie!

Are you sure that you checked my attachement well??



in my example Sheet1 & Sheet 2. In sHEET 1, b1=empty. In Sheet2>>E1, i put any value. No error. In the range that i have lock, i can put any value, because Sheet1!BI>>>IS NOT "PRESENT"!!

2ND & 2ND Sheets: Sheet1!b1=something else(not "present". In Sheet2!e1, put any value you like.In the range that i have lock, i can put any value, because Sheet1!BI>>>IS NOT "PRESENT"!!
Post4


In this case if Sheet1!b1="present" and Sheet2!e1=EMPTY, then you can put no value in range A1:P30.

Pls let me know!

[Fotis1991]

Hi

But what I need is IF Sheet1!B1="present" then users can enter any value (or leave blank) any other cells except for Sheet2!E1, they HAVE TO enter something into Sheet2!E1.

If the user don't type anything in Sheet2!E1;what will happened?

It's not easy to force a user to type someting...... Sorry....

[valkie]

If the user don't type anything in Sheet2!E1;what will happened?

The point is to REMIND user that a certain condition has been met, he/she has to enter a value into this cell. Therefore, once they reach this cell, they cannot tab away from it without entering something first. I guess if they intentionally want to leave them blank, they can do it by mouse clicking on another cell(?).

Thanks, Fortis.
Valkie

[Fotis1991]

Hi Valkie

Therefore, once they reach this cell, they cannot tab away from it without entering something first

I do not know the way to do such a thing...

My idea(solution), is using the validation in a range..,to don't allow the users to type anything, if first don't type something in E1.

So this is it....

[valkie]

Thank you very much, Fortis, for all your efforts and time!
Very much appreciated!

Valkie

[Fotis1991]

You are welcome!