Data Validation

[patni]

Hey guys i have this code in data valididation. this basically does not allow a user to enter any data in Range R11:R30 if column O and P does not have data

=COUNTBLANK($O11:$P11)=0

So for example :
If O11 has data in it.
P11 does not have any data in it.
Now if i try to type any data in R11 , then it must stop me from inputting any data.

So i get a error message that user has restricted any data entry in cell. However the data still gets inputted even though I get error message.

Is it possible to make sure that data does not get inputted at all?

 

[Joe4]

Re: Data Validation help

However the data still gets inputted even though I get error message.

I cannot reproduce that. It does not allow me to enter anything.
Can you tell us what options/entries you made in setting up the Data Validation on all three tabs of Data Validation (Settings, Input Message, Error Alert)?
That way, I can try to reproduce your rule, exactly as you have it.

 

[patni]

Re: Data Validation help

Hey Joe,
Thank for your reply.

Basically what I want is that:

Both Cell O11 and P11 needs to have some data in it , in order for me to type in any data in Cell R11.
So if cell O11 has data but P11 doesnt have. Then i should not be allowed to type in any data in R11.
vice versa.

So i can only type in data in R11 if both O11 and P11 has data in it.

This formulae

[COLOR=#333333]=COUNTBLANK($O11:$P11)=0[/COLOR]

I have used in data validation for the entire range where I want to implement the abobe scenerio.

So for example .

Both o11 and P11 are blank. Now I try to write some data in R11. This formulae shows me pop up message that user has restricted any data entry in it . (the basic data validation error message) . However the data gets inputted in the cell after the pop up error screen.

I dont want it to get inputted at all.

Hope I was able to make you understand.
I would be happy to share my excel file if I could

 

[sparky2205]

Re: Data Validation help

Hi patni,
what Joe is asking here is what are your settings for the third tab in Data Validation?
You have three options:
Stop, Warning & Information each of which allow different things.
If you want to have a hard stop you must select the Stop option in this tab.

 

[patni]

Re: Data Validation help

Hey sparky,

Now i solved it. Thank you so much... I used the hard stop and it worked perfectly.

Thank you