Large Contains Look-up with Multiselect Field
I have a birthday request form where birthday bags are made and then stored. To track where the requests are stored, I have a multi select column named: Inventory Slot Number where a user can select 1-500 and can select multiple numbers like 1 and 2 for example. We get 5000+ requests a year so there will be alot of rows in this sheet with revolving inventory. We need mutli-select since some items have a large item that is in a bulk section slot.
On another sheet I have a list of the 500 numbers 1-500 and want to understand which slots are taken for slot availability management so I have a Slot column listing 1-500 and then a slot availability column to say whether it is open or being used.
I looked at this post but am having trouble applying since its only a select number of dropdowns
https://community.smartsheet.com/discussion/74170/contains-with-multi-select-column
Right now I have a slot helper column on sheet 1: =IF(ISBLANK([Inventory Slot Number]@row), "", SUBSTITUTE([Inventory Slot Number]@row, " ", ""))
On Sheet 2 - =COUNTIFS({2024 Case Worker & Foster Family Birthday Range 1}, IF(HAS(@cell, "1"), "true", "false") = "true", Slot:Slot, IF(HAS(@cell, "1"), "true", "false") = "true")
where range 1 =. Inventory slot number column and it's not letting me update "1" to the slot at row
Answers
-
Are you able to provide screenshots for context?
-
Sure, thanks for taking a look. There's 2 sheets:
2024 Case Worker & Foster Family Birthday Requests: For context this is where people can request a birthday bag be made for foster kids. Once the bag is made it is put in a slot for pick-up. Sometimes the toys are big and need multiple slots so this is a multiple select field. Volunteers make the bags so multi select is the easiest to have for them.
Then there is Completed bag inventory slot management sheet. This is a list of the 468 slots/rows and I want a consolidated sheet that can show slot availability so we can check and make sure we are not running out of room. For slot availability I just want it to say available or assigned to bag.
It seems simple but has been so hard to work with!
-
Ok. And how do you determine when a slot (or slots) has/have opened back up?
-
Once the bag is picked up (based on bag pick up date column not being empty), the slot number is changed to "no slot assignment" based on a smartsheet automation. I would prefer for it to be changed to blank but was not able to do this in an automation that I could figure out.
-
Hi Paul, I saw a similar string and was able to figure it out with the other persons response. Thank you so much for your help on this! https://community.smartsheet.com/discussion/comment/417251#Comment_417251
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!