MusterDuster
New Member
Hi All,
Hope you're healthy and well. Long time fan of Chandoo...learned a ton from him, now it's my turn to ask a question
I need help with this nasty, nasty formula...no dummy data for now, as doing both that and correcting the syntax of the formula would be very time consuming.
Here are the issues:
1) The issue is that the formula below is only pulling "negative" formula parameters (For example, as stated below in formula, "Invalid CID/Account Setup Incomplete"...highlighted in red) The formula is NOT picking up instances where a positive parameter is met (For example, as stated above in the formula, "Valid CID/Account Setup, Website Working"...also highlighted in red) Because of this, it makes me thing it's a logic issue with the formula and it's stopping somewhere before going through all the pieces of the formula.
2) The formula is not acting like a proper array formula...requires to be drug down the column, which it shouldn't require.
Below is the formula separated for readability
--------
{(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 and well. Long time fan of Chandoo...learned a ton from him, now it's my turn to ask a question
I need help with this nasty, nasty formula...no dummy data for now, as doing both that and correcting the syntax of the formula would be very time consuming.
Here are the issues:
1) The issue is that the formula below is only pulling "negative" formula parameters (For example, as stated below in formula, "Invalid CID/Account Setup Incomplete"...highlighted in red) The formula is NOT picking up instances where a positive parameter is met (For example, as stated above in the formula, "Valid CID/Account Setup, Website Working"...also highlighted in red) Because of this, it makes me thing it's a logic issue with the formula and it's stopping somewhere before going through all the pieces of the formula.
2) The formula is not acting like a proper array formula...requires to be drug down the column, which it shouldn't require.
Below is the formula separated for readability
--------
{(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","")))),""),""))}