Collect values from another sheet
I have a worksheet with 5 columns that are multiselect dropdowns. The dropdown for each of the 5 columns is exactly the same; 400+ Store #'s.
I would like to collect in another sheet ALL of the selections from one column and compare those to the actual list of Store #'s. The object is to identify if a store # has been inadvertently left out.
I'm really not sure how to go about this. I thought JOIN, but that just gives me a string of #'s in one cell. I'm not sure how I would compare that to a list of Store #'s and determine if there were any missing.
Best Answer

@PeggyLang You can add a checkbox column to the sheet that has all of your store numbers. In this checkbox column, you'd put a formula similar to what's below (mine only has the first two of the five parts…you'd need to finish that out). Finally, you could filter on that checkbox column to see only blanks which would be your missing stores.
=IF(COUNTIFS({OtherSheet_ODDMTHWK1TOC}, CONTAINS([ColumnInThisSheetWithStoreNumbers]@row, @cell)) > 0, 1, IF(COUNTIFS({OtherSheet_NextColumn}, CONTAINS([ColumnInThisSheetWithStoreNumbers]@row, @cell)) > 0, 1, 0), then continue with the other three columns)
Hope this helps!
Answers

@PeggyLang You can add a checkbox column to the sheet that has all of your store numbers. In this checkbox column, you'd put a formula similar to what's below (mine only has the first two of the five parts…you'd need to finish that out). Finally, you could filter on that checkbox column to see only blanks which would be your missing stores.
=IF(COUNTIFS({OtherSheet_ODDMTHWK1TOC}, CONTAINS([ColumnInThisSheetWithStoreNumbers]@row, @cell)) > 0, 1, IF(COUNTIFS({OtherSheet_NextColumn}, CONTAINS([ColumnInThisSheetWithStoreNumbers]@row, @cell)) > 0, 1, 0), then continue with the other three columns)
Hope this helps!

@Kelly P.
WOW!
I was SO overthinking this solution. You hit the nail right on the head. Easy peasy, thanks for showing me the trees in the forest!!!! Worked like a charm. 
@PeggyLang Happy to help!! 😊
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.6K Get Help
 403 Global Discussions
 215 Industry Talk
 455 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 56 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 296 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!