Data Validation

patni

Board Regular
Joined
Jul 22, 2018
Messages
58
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
Code:
=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?
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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.
 
Upvote 0
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
Code:
[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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top