Large Contains Look-up with Multiselect Field

ChristineMesa

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

Paul Newcome

Are you able to provide screenshots for context?

ChristineMesa

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!

Screen shots.docx

Paul Newcome

Ok. And how do you determine when a slot (or slots) has/have opened back up?

ChristineMesa

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.

ChristineMesa

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