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
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.
Will this work for you? Shout out if you need any help
Kelly
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)
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.
Will this work for you? Shout out if you need any help
Kelly
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)
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
