How can my index/match formula include a column that has multiple values
Ok so I downloaded an inventory management template that is awesome! When people submit a request form for a ppe item they need, it has index/match forumlas to show us what Current Inventory remains. Its great!
However, they are only able to select one ppe item from the drop down list. And I need them to be able to select more than one (sometimes up to 20+ items). Currently when they do that, the index/match formulas break and I cannot figure out how to make them work to show us the counts when multiple items are selected.
Is this possible? Please help
Here is my main intake sheet and these cells break:
This cell has a index/match formula telling us what category the PPE is from but if the PPE Requested column has multiple items in it, the category breaks
and when that breaks, the other formulas break:
Answers
-
So, the issue you are running into with INDEX/MATCH is that it can only grab 1 piece of data at a time.
In addition, if there are multiple items in the PPE Requested, you would need multiple order qty if they are not the same qty. There's some ways to do this but it will take a lot of work and adding of either multiple columns, or a filtering sheet that you'll need to set up yourself.
-
Understood. What is the best way to do this then ? I started to add columns but that will be like 72 additional columns on this sheet 😯
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!