Forum Discussion

JBF_54's avatar
JBF_54
Brass Contributor
Mar 02, 2020

Avoiding a SPILL Error

Hello,

I have a spreadsheet containing a table where each row is a separate vendor. On the far right I have a column that checks various fields to make sure they are filled in properly and results in either "OK", or messages indicating what data is missing. The following formula is used:

 

=IF(TEXTJOIN("
",TRUE,IF(A2="","Missing Full Name",""),IF(B2="","Missing Company Name",""),IF(AND(D2="",E2=""),"Missing Phone Number",""),IF(G2="","Missing Address",""),IF(J2="","Missing Email",""))="","OK",TEXTJOIN("
",TRUE,IF(A2="","Missing Full Name",""),IF(B2="","Missing Company Name",""),IF(AND(D2="",E2=""),"Missing Phone Number",""),IF(G2="","Missing Address",""),IF(J2="","Missing Email","")))

 

NOTE 1: Even though the column containing this formula is part of the table, I have to use the cell references (A2,B2, etc.). If I used the table column reference (with the square brackets), then I get SPILL errors.

QUESTION 1: Is there a way to use the square-bracket references and still get row-by-row (non-array) comparisons?

 

NOTE 2: The general form of this formula is IF <big mess>="", then "OK", else <big mess>

QUESTION 2: Any suggestions on how to use only 1 <big mess>? Currently things are messy enough when I'm only checking a handful of fields, but eventually I'll likely be checking a dozen or more fields.

 

Sample spreadsheet is attached ... thanks in advance.

excel
Formulas and Functions
office 365

4 Replies

  • Allen's avatar
    Allen
    Icon for Community Manager rankCommunity Manager
    Dec 11, 2024

    Sorry folks, due to the type of data in the attachment I have had to remove the attachments.

    Please do not post PII information (or information that looks like PII) in the Microsoft Tech Community

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Dec 11, 2024

      That's 4-years old post, have no idea what was in attachment. Based on texts we spoke about Excel.

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Mar 02, 2020

    JBF_54 

    If to check every field, not separate ones, that could be

    =IF(SUMPRODUCT(--(LEN(Vendor_TBL[@[Full Name]:[Email]])<2)),TEXTJOIN(", ",1,IF(LEN(Vendor_TBL[@[Full Name]:[Email]])<2,Vendor_TBL[[#Headers],[Full Name]:[Email]],"")),"Ok")
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Mar 02, 2020

    JBF_54 

    There is no spill error with structured references

    =IF(TEXTJOIN("
",TRUE,IF([@[Full Name]]="","Missing Full Name",""),IF([@Company]="","Missing Company Name",""),IF(AND([@[Mobile Phone]]="",[@[Business Phone]]=""),"Missing Phone Number",""),IF([@[Business Address]]="","Missing Address",""),IF([@Email]="","Missing Email",""))="","OK",
TEXTJOIN("",TRUE,IF([@[Full Name]]="","Missing Full Name",""),IF([@Company]="","Missing Company Name",""),IF(AND([@[Mobile Phone]]="",[@[Business Phone]]=""),"Missing Phone Number",""),IF([@[Business Address]]="","Missing Address",""),IF([@Email]="","Missing Email","")))

    If inform what exactly was missed then compare field by field

Resources