Data validation with condition

[Beneindias]

Hello,

I'm trying to do something that i'm honestly not confident that can work as I imagine.

Lets assume that I have cell A1, where I can have "Sunday" or "Weekday"
In cell C1, tha data will depend on what is in cell A1,

So, if I have "Sunday" in cell A1, I want cell C1 to be empty, but if in A1, I have "Weekday", I want to input something.

I was trying to create a Data Validation condition, but it's not working properly, because it's only validating empty cell.

I tried with this validation:

=IF(OR($B8="Holiday";$B8="Saturday";$B8="Sunday");"")

Is it possible to do a data validation like I am trying?

Can any of you help me with this?

Thank you all

 

[etaf]

your formula does not match the words

data validation

A1 = "weekday"

But what if cell A1 has other text in - is it ONLY weekend that allows data to be entered

Not sure i fully understand

 

[awoohaw]

by something do you mean any random value/text? or something that is in another list somewhere?

 

[Phuoc]

Try this

Book1
	B	C
8	Holiday
9	Monday	123
10	Saturday
11
12
13
14
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
C8:C14	Custom	=($B8<>"Holiday")*($B8<>"Saturday")*($B8<>"Sunday")*($C8<>"")

 

[Beneindias]

Ok, I will try to be more specific.

In cell A1, I can have:
- "Saturday";
- "Sunday";
- "Holiday";
- "Weekday".

if A1 = Weekday -> I can input text/value (actualy it will be time of departure)
if A1 = Sat, Sun or Holiday -> I can't input value/text (only validates empty cell)


Hope it's easier to understand this way.
I was trying to simplify.

 

[Beneindias]

Try this

Book1
	B	C
8	Holiday
9	Monday	123
10	Saturday
11
12
13
14
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
C8:C14	Custom	=($B8<>"Holiday")*($B8<>"Saturday")*($B8<>"Sunday")*($C8<>"")

This worked.

Thanks for your help and to the others that tried to help, too.

Have a nice week

 

[etaf]

what other text can be added - otherwise

=A1="weekday"
will only allow data entry to c1 if weekday is entered in a1

 

[awoohaw]

@Phuoc great solution I've never seen custom data validation formulas.

 

[Beneindias]

what other text can be added - otherwise

=A1="weekday"
will only allow data entry to c1 if weekday is entered in a1

Thanks for your sugestion, too.

Don't know how I didn't think of this

 

[etaf]

you are welcome