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
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