Create a list from check marks in a grid
I need to pull data from a grid organized like this (with yellow items being being column headings):
into a list that is structured like:
I am stumped on how to do it. I do not own the original data so I cannot restructure it into a different form.
Any tips?
-Matthew
Answers
-
You could use a cross-sheet JOIN(COLLECT formula to Join together in one cell all of the different Teams associated with each Resource, but the formula wouldn't be able to parse the details into multiple rows.
Here's an example of what that might look like:
Note that for each Resource you'll need to create a unique cross-sheet reference to that specific column. In my formula I'm presuming that your Resource columns are checkbox columns, where 1 = a checked box.
Here's the formula structure:
=JOIN(COLLECT({Team}, {Resource 1}, 1), ", ")
Will this work for you?
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Unfortunately I need the pulled data in multiple rows so that I can use that to pull from yet another set of data in order to create a report.
-
Allow for blank answers and just filter them out.
=IF([Resource 1]1 = 1, Team1, "")
=IF([Resource 1]2 = 1, Team2, "")
=IF([Resource 1]3 = 1, Team3, "")
etc
=IF([Resource 2]1 = 1, Team1, "")
=IF([Resource 2]2 = 1, Team2, "")
=IF([Resource 2]3= 1, Team3, "")
etc
ETC
-
The source data is much more complicated than I presented. There are thousands of "teams" and a dozen or so "resources". So far for each resource I have a sheet that pulls a list of teams using Index and Collect.
This gives me a list of teams I can reference for the metrics associated with each resource but does not allow me to build the metrics by team.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!