Can I write a cross-sheet formula that works if one has one and the other has many?

Melissa Selzler

Here is the use case:

Smartsheet A: Lists selected clients once per row (includes a column with a unique client ID). I created a drop-down column for each product with yes or no picklist.

Smartsheet B: Lists clients on many rows (includes a column with a unique client ID). I developed a formula to enter "Yes" IF the the row included the year 2023 AND used "X product"

I want a formula in Smartsheet A that will enter "Yes" for "X product" IF any row containing the Client ID in Smartsheet B has a "Yes".

For example, Client ID = 0001 may contain 10 rows on Smartsheet B. If only 1 row for that client has a "Yes" in "X product" column, I want a "Yes" in "X product" column on Smartsheet A.

Is there a formula that can do that for me?

I would expect the following output for Client-Product Tracking A:

0001: X-Product = Yes; Y-Product = No

0002: X-Product = No; Y-Product = Yes

0003: X-Product = Yes; Y-Product = Yes

0004: X-Product = No; Y-Product = Yes

0005: X-Product = Yes; Y-Product = No

0006: X-Product = No; Y-Product = Yes

0007-0010: 0005: X-Product = No; Y-Product = No

Kelly Moore

Hey @Melissa Selzler

=IF(COUNTIFS({sheet B client ID column}, [Client ID]@row, {sheet B X Product column}, "Yes")

This formula would be replicated in your Y column referencing the B sheet Y column

You will need to manually insert the cross sheet references from the formula window, you cannot simply copy paste this formula into your sheet. If you are unfamiliar with cross sheet references you might find this link helpful.

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

Will this work for you? Shout out if you need any help

Kelly

Melissa Selzler

Thank you so much. The formula above did not work right away. However, I was able to make some adjustments and got it to work. I could not have done it without you. :-)

=IF(AND(NOT(ISBLANK([Client ID]@row)), COUNTIFS({Subscriptions Customer ID}, [Client ID]@row, {Subscriptions Product X}, 1) > 0), 1, 0)

Kelly Moore

Hey @Melissa Selzler

=IF(COUNTIFS({sheet B client ID column}, [Client ID]@row, {sheet B X Product column}, "Yes")

This formula would be replicated in your Y column referencing the B sheet Y column

You will need to manually insert the cross sheet references from the formula window, you cannot simply copy paste this formula into your sheet. If you are unfamiliar with cross sheet references you might find this link helpful.

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

Will this work for you? Shout out if you need any help

Kelly

Melissa Selzler

Thank you so much. The formula above did not work right away. However, I was able to make some adjustments and got it to work. I could not have done it without you. :-)

=IF(AND(NOT(ISBLANK([Client ID]@row)), COUNTIFS({Subscriptions Customer ID}, [Client ID]@row, {Subscriptions Product X}, 1) > 0), 1, 0)

Kelly Moore

grrrrrrrrrrrrrr. Yes, when pasting in the formula I forgot to add the criteria portion of the IF. That's what happens to me when I'm working these questions very late at night. Sorry for the omission but glad you understood what I meant.

kelly