Nested IF in Checkbox Column when Referencing Another Sheet


Have an issue I cannot seem to resolve to automate a checkbox.

I have a "Content" sheet that is data coming from a source outside Smartsheet. Relevant columns look like this:

I have a separate sheet (we'll call it "Product Family") that analyzes the data on the Content sheet. Relevant columns look like this:

I want to automate the checkbox columns starting at "Data Sheet" and ending at "Demo." The box should be checked if:

  1. The Content Type in the Content Type column on the Content sheet matches the content type in the column headers on the Product Family sheet; AND
  2. The Tag from the Tags column on the Product Family sheet appears in any of the columns in the range spanning Product Family through PF5 on the Content sheet.

I tried this formula:

=IF(AND(CONTAINS([Tags]@row, {Product Family Range 1}), {Product Family Range 2}, "Data Sheets"), 1, 0)

(Range 1 is Product Family through PF5, and Range 2 is the Content Type column)

I get an INVALID DATA TYPE error. The checkbox columns are NOT restricted. Not sure if it's relevant, but the data in the Tags column on the Product Family sheet is via a cell link to another sheet not shown here.

Any suggestions for how to resolve?

Best Answer


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I'm not sure I understand your first criteria. Can you please expand on that?

  • Danielle Arteaga
    Danielle Arteaga ✭✭✭✭✭✭

    Paul -

    In the Tags column, each row represents a different product. Each product will have certain content associated with it: a Data Sheet, an Ordering Guide, a BDM, a TDM, etc. However, products don't always have EVERY content type. What I'm looking to achieve is a master list of products (Tags) and the content types they should have.

    As a first step toward figuring out which products have which content, I want the sheet with the Tags column (second screenshot above) to talk to the sheet with the Content Types column (first screenshot above) and to check the boxes in each row when there is a match between the Tag and Content Type.

    For example, if "UCSB" appears in any column between "Product Family" and "PF5" and the Content Type on that row is "Data Sheet," the checkbox in the "Data Sheet" column on the row for UCSB would toggle on.

  • Danielle Arteaga
    Danielle Arteaga ✭✭✭✭✭✭

    This worked! Thank you so much, Paul. Really appreciate your help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!