Multi-select string operation
Hi all,
I have three columns which have the same drop down options: requested, received and pending. I would like the "pending" column to have all options from the "requested" column except for what's in the "received" column.
For example: under "requested": option 1,2,3,4; under "received": option 1,2; under "pending" automaticlly pop up option 3,4.
Basiclly I want to do a minus operation between "requested" and "received" and the results are also in multi-select form.
Thanks!
Answers
-
Hi @Amanda0220
I'm not quite sure I understand your set-up, but I've built a tester sheet to try and replicate what you described. Essentially, it sounds like you want the value of "Pending" to appear, but only if there is content in the original Requested column and there is not the selection of "Received" in the Received column, is that correct?
If so, you can use an IF statement with an AND function to look for those two criteria:
=IF(AND(Requested@row <> "", Received@row <> "Received"), "Pending", "")
<> means "not", so I'm looking to see if the Requested column is not blank, and that the Received column is not "Received". Otherwise, it will return blank:
You may want to make this a column formula (see here) so that no one tries to manually select something else, which would break the formula.
Let me know if I've misunderstood your set up or if I can help further! It would be useful to see screen captures of your sheet if you need more help, but please block out any sensitive data.
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hi @Genevieve P
Thanks for your detailed reply, which gives me an idea to think about it differently. I appreciate it!
I add a screenshot below hope to make myself clear. For each row, the "Requested" and "Received" cells are manually entered by users, and I would like the "Pending" cell to automatically appear what's in the "Requested" minus what's in the "Received" if that's possible.
-
Hi @Amanda0220
Here is a possible solution using the substitute formula. The formula first checks to see if the count of terms in the Requested and Received columns are equal- it's not checking if the contents are the same. If the two cells are equal in count, it enters Received in your Pending column. Without that check, the Pending cell would end up blank if the two columns equaled each other - which might be ok? Omit the IF-statement if a blank cell doesn't matter.
The Substitute function searches the Requested@row for the Received@row text. When it finds that text, it replaces that text with a blank, - which essentially gives you a Requested - Received outcome.
=IF(COUNTM(Requested@row) <> COUNTM(Received@row), SUBSTITUTE(Requested@row, Received@row, ""), "Received")
No IF statement
=SUBSTITUTE(Requested@row, Received@row, "")
Hope this helps
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.1K Get Help
- 414 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 141 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!