MusterDuster
New Member
- Joined
- May 11, 2021
- Messages
- 3
- Office Version
- 2016
- Platform
- Windows
- MacOS
Hello All,
Hope you're healthy, safe and well.
Long time lurker of the forum...has taught me alot, so thank you.; Allow me to welcome you to my first post.
Below I have a formula I wrote that is being problematic...only caveat is that it needs to stay as an array formula.
The main issue is that my formula isn't validating (and populating) any notation in the appropriate cells when 'positive' criteria has been met...(I.E. "Website Working" for example). It's only populating 'negative' criteria (I.E. "Invalid CID/Account Setup Incomplete" for example), when it should be populating both. Because of this, it makes me thing it's the logic of the formula and I'm stumped!
The second issue is that the formula not exhibiting the behavior an array should. The formula is being required to be drug (dragged) down, which shouldn't be necessary with an array.
Please let me know if there's anything I can add to the post...the data is sensitive, so if that's needed, I'll need to change things within the sheet itself.
I've spaced out the formula for readability.
-----------
=ArrayFormula(IF(P2<>"",IF(AD2<>"Yes",IF(AND(X2="Verified",Z2="Yes",AA2="Yes"), IF(W2="Yes","VALID LICENSE: "&Y2 &" "&"VALID REGISTRATION: "&Y2&" "&"VALID CID / ACCOUNT SETUP: "&Z2&" "&"WEBSITE WORKING: "&AA2,
IF(W2="Not Required","LICENSE: NOT REQUIRED" &" "&"VALID REGISTRATION: "&Y2&" "&"VALID CID / ACCOUNT SETUP: "&Z2&" "&"WEBSITE WORKING: "&AA2,
IF(W2="Missing","LICENSE ISSUE, ", IF(W2="No","LICENSE ISSUE, ","")))),IF(X2="Not Verified","SoS REGISTRATION ISSUE, ", IF(Z2="No","INVALID CID / ACCOUNT SETUP INCOMPLETE, ",IF(AA2:AA="No","WEBSITE NOT WORKING","")))),""),""))
Hope you're healthy, safe and well.
Long time lurker of the forum...has taught me alot, so thank you.; Allow me to welcome you to my first post.
Below I have a formula I wrote that is being problematic...only caveat is that it needs to stay as an array formula.
The main issue is that my formula isn't validating (and populating) any notation in the appropriate cells when 'positive' criteria has been met...(I.E. "Website Working" for example). It's only populating 'negative' criteria (I.E. "Invalid CID/Account Setup Incomplete" for example), when it should be populating both. Because of this, it makes me thing it's the logic of the formula and I'm stumped!
The second issue is that the formula not exhibiting the behavior an array should. The formula is being required to be drug (dragged) down, which shouldn't be necessary with an array.
Please let me know if there's anything I can add to the post...the data is sensitive, so if that's needed, I'll need to change things within the sheet itself.
I've spaced out the formula for readability.
-----------
=ArrayFormula(IF(P2<>"",IF(AD2<>"Yes",IF(AND(X2="Verified",Z2="Yes",AA2="Yes"), IF(W2="Yes","VALID LICENSE: "&Y2 &" "&"VALID REGISTRATION: "&Y2&" "&"VALID CID / ACCOUNT SETUP: "&Z2&" "&"WEBSITE WORKING: "&AA2,
IF(W2="Not Required","LICENSE: NOT REQUIRED" &" "&"VALID REGISTRATION: "&Y2&" "&"VALID CID / ACCOUNT SETUP: "&Z2&" "&"WEBSITE WORKING: "&AA2,
IF(W2="Missing","LICENSE ISSUE, ", IF(W2="No","LICENSE ISSUE, ","")))),IF(X2="Not Verified","SoS REGISTRATION ISSUE, ", IF(Z2="No","INVALID CID / ACCOUNT SETUP INCOMPLETE, ",IF(AA2:AA="No","WEBSITE NOT WORKING","")))),""),""))
Last edited by a moderator:
