Create a sprint report based on column values by repeating the row
Hi Community!
I have a sheet with distinct rows representing a task and a column indicating various design/build activities by sprint. I need to summarize this data into a new report or sheet to see all the work per sprint. I don't need any other columns/dates autopopulated.
Source sheet sample
Desired report sample
Best Answer
-
Based on your source sheet set-up, you would need to evaluate each column individually to see if it contains that specific Sprint number (ex. "3.4"), and then return the Dependency for that row from that column if it found a match.
You could do this with a JOIN(COLLECT formula, however this won't be able to parse multiple values down your column into new rows, it would JOIN together all the Dependencies into one cell.
You would need multiple JOIN(COLLECT formulas, one per column, like so:
=JOIN(COLLECT({Dependencies Column}, {Column 1}, 3.4), ", ") + "', " + JOIN(COLLECT({Dependencies Column}, {Column 2}, 3.4), ", ") + "', " + JOIN(COLLECT({Dependencies Column}, {Column 3}, 3.4), ", ") ... etc
An alternative would be to set up all possible rows per-sprint in one sheet, then use a COUNTIFS formula to check each column to see if that value appears for that Dependency, and if it does, check a box.
=IF(Parent@row = "", "", IF(SUM(COUNTIFS({Dependency}, [Task Sprint Name]@row, {1st Column}, Parent@row), COUNTIFS({Dependency}, [Task Sprint Name]@row, {Column 2}, Parent@row), COUNTIFS({Dependency}, [Task Sprint Name]@row, {3rd Column}, Parent@row)) > 0, 1, 0))
Then you could use a Report to Group by the Parent / Sprint Column and filter out any of the rows that are not checked.
Cheers,
Genevieve
Answers
-
Based on your source sheet set-up, you would need to evaluate each column individually to see if it contains that specific Sprint number (ex. "3.4"), and then return the Dependency for that row from that column if it found a match.
You could do this with a JOIN(COLLECT formula, however this won't be able to parse multiple values down your column into new rows, it would JOIN together all the Dependencies into one cell.
You would need multiple JOIN(COLLECT formulas, one per column, like so:
=JOIN(COLLECT({Dependencies Column}, {Column 1}, 3.4), ", ") + "', " + JOIN(COLLECT({Dependencies Column}, {Column 2}, 3.4), ", ") + "', " + JOIN(COLLECT({Dependencies Column}, {Column 3}, 3.4), ", ") ... etc
An alternative would be to set up all possible rows per-sprint in one sheet, then use a COUNTIFS formula to check each column to see if that value appears for that Dependency, and if it does, check a box.
=IF(Parent@row = "", "", IF(SUM(COUNTIFS({Dependency}, [Task Sprint Name]@row, {1st Column}, Parent@row), COUNTIFS({Dependency}, [Task Sprint Name]@row, {Column 2}, Parent@row), COUNTIFS({Dependency}, [Task Sprint Name]@row, {3rd Column}, Parent@row)) > 0, 1, 0))
Then you could use a Report to Group by the Parent / Sprint Column and filter out any of the rows that are not checked.
Cheers,
Genevieve
-
Thank you! I tried out your formula and it did work but did not give me exactly what I needed. I ended up adding a sheet summary field and linking it to an "IF" formula to filter the column results by sprint and then created a sheet filter to show me all rows that did not contain blanks.
-
I'm glad you were able to figure out something that works for you! 🙂
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives