Data Validation

[patni]

Hey guys ,
I need help with data validation.

I want to allow user to type data in V11 and W11 only if O11 and P11 has data in it

So if cell O11 and P11 does not have any data in it then a user cannot enter any data in V11 and W11

I will apply data validation to V11:W31.

Data in O11 is derived using V Look Up
Data in P11 is manually types by user

Can anyone please help me here?

Thank you

 

[mumps]

Re: Data Validation help

Do you want to allow input into V11 if O11 has data and allow input into W11 if P11 has data or do W11 and P11 both have to have data in order to allow input into V11 and O11? Also, what data validation do you have in V11:W31? Please clarify.

 

[patni]

Re: Data Validation help

hey mumps,
Thank you for your reply

If both O11 and P11 has any data in it then i want it to allow users to type in W11 ann V11

Both cells in O11 and P11 needs to have data then only users can type in W11 and V11

Example : O11 does not have any data
P11 has some data in it
In this case user cannot type in any data on V11 and W11

Currently I dont have any data validation in V11:W31

 

[tyija1995]

Re: Data Validation help

Select cells V11:W11

Data tab -> Data Validation -> Allow: Custom -> Formula: =COUNTBLANK($O$11:$P$11)=0 -> OK

This states that you can only enter values in V11 & W11 where O11 and P11 are not blanks

 

[patni]

Re: Data Validation help

Heytyija1995,

The formulae works perfectly.
Thank you so much

 

[patni]

Re: Data Validation help

Hey mumps,

I got the solution.
Thank you for your time.

 

[mumps]

Re: Data Validation help

Glad it worked out.

 

[patni]

Re: Data Validation help

Hey tyija1995,

I am facing a problem
This formulae works perfectly for row 11
How do i do it for entire range V11:W31
I selected the entire range ,and in data validation , pasted that code. It works for row 11 but not for others.
I want to select entire range and apply that formulae

 

[tyija1995]

Re: Data Validation help

Hey,

So you want V11:W31 to only allow values if O11:P11 are both not blank? If so remove your current data validation (to avoid any confusions) and select the range V11:W31 -> Apply same steps as my previous post. (=COUNTBLANK($O$11:$P$11)=0) *notice the absolute reference is important and the deciding factor between the option below*

If you want it on a row by row basis, I.e. you can only enter in V:W (where n is the row number) if cells O:P are both non blank then with V11:W31 highlighted use this formula:

=COUNTBLANK($O11:$P11)=0

This for example will stop a user entering in, say, V25:W25 if O25:P25 contain at least 1 blank.