Data Validation Custom IF Formula.

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
2,040
Office Version
  1. 2019
Platform
  1. Windows
Hey all,

Using data validate to check cell's A2 value for False and True. If False then allow whole numbers, but if True then allow only an "empty cell".

The above data validation is required in cell B2 till B10 range.

Is possible?

Thanks and will appreciate.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
And what should the validation do when A2 is neither True nor False?

J.Ty.
 
Upvote 0
And what should the validation do when A2 is neither True nor False?

J.Ty.

Thank you for asking but I am not okay with how the data validation works in Excel, Especially when it comes down to copy /pasting on a cell which has a validation rule set already. This would remove the validation and so would dragging the adjacent cell onto it...

I will be moving on from data validation to a Vba script soon..

To answer your question, the cell has a formula that allows for only True or a False Value. There is no chance to get an error as it the formula on that is =SUMPRODUCT(--(G1:K8<>0))=0 , and the range G1:K8 allows only numbers with using data validation.
 
Upvote 0
Indeed, standard validation does not survive copying and pasting. VBA is not my area, so I leave it for somebody else.

Good luck!

J.Ty.
 
Upvote 0
Place data validation in B2 and copy down

A2 is absolute
Data validation \Allow : Custom
Formula : =AND($A$2=FALSE,INT(B2)=B2)

A2 is relative
=AND(A2=FALSE,INT(B2)=B2)

but if True then allow only an "empty cell".
Do you mean that if B2 already contains a value it cannot be amended if A2 = TRUE
If so this can only be done with VBA
If that is the case I need to know whether A2 is absolute or relative
 
Last edited:
Upvote 0
Using data validate to check cell's A2 value for False and True. If False then allow whole numbers, but if True then allow only an "empty cell".

I am reading this again and this I misread it the first time so I think you can ignore my previous post
VBA will be required

Are you saying
B2:B10 can only contain integers
and IF A2 = FALSE then B2:B10 can be amended
or IF A2 = TRUE then B2:B10 can not be amended if they contain a value

Is A2 relative or absolute ?
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

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