Can I write a crosssheet formula that works if one has one and the other has many?
Here is the use case:
Smartsheet A: Lists selected clients once per row (includes a column with a unique client ID). I created a dropdown 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 ClientProduct Tracking A:
0001: XProduct = Yes; YProduct = No
0002: XProduct = No; YProduct = Yes
0003: XProduct = Yes; YProduct = Yes
0004: XProduct = No; YProduct = Yes
0005: XProduct = Yes; YProduct = No
0006: XProduct = No; YProduct = Yes
00070010: 0005: XProduct = No; YProduct = No
Best Answers

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)
Answers

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
Help Article Resources
Categories
Check out the Formula Handbook template!