Nested IF in Checkbox Column when Referencing Another Sheet

Danielle Arteaga

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?

Paul Newcome

Ok. You are going to want to use a helper column in the first screenshot (that can later be hidden to keep the sheet clean) where you join the [Product Family] through [PF5] columns.

=JOIN([Product Family]@row:[PF5]@row, ",")

Then the formula in the [Data Sheet] column in the second screenshot would be something along the lines of...

=IF(COUNTIFS({Other Sheet Content Type Column}, "Data Sheet", {Other Sheet Helper Column}, CONTAINS(Tags@row, @cell)) > 0, 1)

Paul Newcome

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

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.

Paul Newcome

Ok. You are going to want to use a helper column in the first screenshot (that can later be hidden to keep the sheet clean) where you join the [Product Family] through [PF5] columns.

=JOIN([Product Family]@row:[PF5]@row, ",")

Then the formula in the [Data Sheet] column in the second screenshot would be something along the lines of...

=IF(COUNTIFS({Other Sheet Content Type Column}, "Data Sheet", {Other Sheet Helper Column}, CONTAINS(Tags@row, @cell)) > 0, 1)

Danielle Arteaga

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

Paul Newcome

Happy to help! 👍️