Custom formulas within Data Validation

LaughingStock

New Member
Joined
Oct 4, 2016
Messages
2
First time poster, have received a lot of help from reading other posts, so thanks in advance!

Let's say that A1 is where a user can put in a date, such as 1/1/16, but is left blank otherwise. Let's then say that cell B1 is set as follows =IF(ISBLANK(A1),"N/A",480-SUM(P:P)). Let's then say Column C is nothing but user input dates from 1-infinity

I want all cells in column D to create a Stop popup based if the following qualifications (in order) are not met:
1. There must be a date entered in $A$1
2. The date entered in C1 must be greater than or equal to the date in $A$1
3. The date entered in C1 must be prior to 1 year after $A$1
4. The sum in $B$1 MUST be greater than or equal to 0

I entered the following formula into the data validation: =IF($B$1="N/A",0,1). I chose this since "N/A" had to be entered if there was no date entered. With only this line in there, the popup correctly was created if no date was entered. I then attempted to do the addition of the other 3 criteria as follows:

=IF($B$1="N/A",0,IF(AND(D1>=$A$1,D1<=EDATE($A$1,12),$B$1>=0),1,0))

This formula CORRECTLY placed a popup qualifications 2-4 were not met, however it ignored qualification 1 altogether. This is clearly not an issue of the formula being too long, and I've placed this in a cell and received the correct 0 or 1 output based on all scenarios. What am I missing?

Thank you for any help in advance
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
The only thing I can think of right now is that the condition is set to put out a 0 if the first condition is right, maybe that's throwing off the formatting? Again, any help is appreciated.
 
Upvote 0

Forum statistics

Threads
1,216,102
Messages
6,128,852
Members
449,471
Latest member
lachbee

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