split multiple items to isolated columns in a new grid
Hi,
In a main grid called 'Grid A', one column contains multiple items selected from a drop down list. For a display purpose, I would like to have these items shown in individual columns (one item sits in a column) in a new grid, called 'Grid B'.
Would someone please share idea to sort it out?
Thank you.😊
Answers
-
Hi @147smartsheet,
could you please add a screenshot for Grid A & Grid B to help me to create this formula for you after you remove any sensitive Data.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Hi @Bassam Khalil ,
Thank you for replying.
As the photos shown, the main grid is 'Minor Project Tracker Live', colum 'High Risks (Next 2 wk)' has a drop down list of values. For example, a project QFBP0050.0 has 3 values in this column.
In a new grid 'Sub report - test', I would like to list these 3 values to individual columns 'High Risk Activity 1', 'High Risk Activity 2'& 'High Risk Activity 3' in the corresponding line.
-
Hi @147smartsheet,
please try the following:
1- in "Sub report - test" sheet create helper column call it "High Risks (Next 2 wk)" and add the following formula on it and convert it to column format formula to collect the multiple risks from the "Minor Project Tracker Live"sheet:
=IFERROR(INDEX(COLLECT({High Risks (Next 2 wk)}, {Project ID}, [Project ID]@row), 1), "")
2- use the following formula for [High Risk Activity 1] in the "Sub report - test" sheet" to subtract the first Value and convert it to column format formula:
=IFERROR(LEFT([High Risks (Next 2 wk)]@row, FIND(CHAR(10), [High Risks (Next 2 wk)]@row) - 1), "")
3- use the following formula for [High Risk Activity 2] in the "Sub report - test" sheet" to subtract the first Value and convert it to column format formula:
=IFERROR(MID([High Risks (Next 2 wk)]@row, FIND(CHAR(10), [High Risks (Next 2 wk)]@row) + 1,FIND(CHAR(10), [High Risks (Next 2 wk)]@row, FIND(CHAR(10), [High Risks (Next 2 wk)]@row) + 1) - FIND(CHAR(10), [High Risks (Next 2 wk)]@row) - 1), "")
4- use the following formula for [High Risk Activity 3] in the "Sub report - test" sheet" to subtract the first Value and convert it to column format formula:
=IFERROR(MID([High Risks (Next 2 wk)]@row, FIND(CHAR(10), [High Risks (Next 2 wk)]@row, FIND(CHAR(10), [High Risks (Next 2 wk)]@row) + 1) + 1, LEN([High Risks (Next 2 wk)]@row)), "")
and here is a screenshot shows the result when i test the soluation:
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 429 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!