Index/Collect/Match help
Hi
I have a sheet that has epics, stories, tasks and I want to extract only the epics to another sheet.
layout is simple
Name IssueType
Epic 1 Epic
Story 1 Story
Story 2 Story
Task 1 Task
Task 2 Task
Epic 2 Epic
Story 3 Story
Story 4 Story
Task 3 Task
Task 4 Task
I want the other sheet to only have
Epic 1
Epic 2
thanks so much.
Bruce Johnson
Director Portfolio, Project Methods & Governance
Veolia North America
Boston, MA
Best Answers
-
=index(collect(summary:summary,epicyes:epicyes,contains("epic",lower(@cell))),1)
will return the summary value for the first epic. change the 1 to any value (or use a count() to automate) to return X value. (you can also switch the ranges to other sheet references to move this to a second sheet.
alternatively you can use a copy or move workflow with a helper checkbox to move all of the row information to a new sheet.
-
something like
=if(not(isblank(epicname@row)),"Epic"+count(epicyes$1:epicyes1),"")
in the second column, then drag down?
Or do you want to start with a special number/actually reference the previous submission
Answers
-
The easiest way to do this would actually be to create a Report that filters down to just the Epics, would that be a possibility in your scenario? (See here for more information.)
Otherwise, you could use an INDEX(MATCH cross-sheet formula, but it would need a unique identifier per-row to find each match and bring through the corresponding epic.
For example, if you used an Index(Match on your current set up with just the 2 columns, it would look like this:
=INDEX({Name Column in other sheet}, MATCH("Epic", {Issue Type Column}))
However this is just looking for one criteria (that the Match is "Epic") so it will return "Epic 1" since that's the first match it will find. When you add this same formula to the cell below, you'll return "Epic 1" again, instead of now "Epic 2" as there are no unique identifiers for this formula to find and bring back - it will always find just the first match.
Are you able to share a bit more about the difference between the two sheets? (Screen captures of each sheet would be preferable, but only if you're able to block out any sensitive data). It would also be helpful to understand the reason and process behind bringing the Epics over... for example, do you need the cells to be linked so that they update if the source updates? (If not, you could set up a Copy Row automation whenever a new Epic type is added.)
Hope this helps,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Hi Genivieve
Thank you for the reply. The Report aspect is easy and I was thinking that - BUT - here is what I am trying to do.
I have a project with 20ish different workstreams - each will have it's own/different IssueType = Epics/Stories/Tasks/Sub-Tasks. We currently have many metrics - gathering - statistics for open/closed/inprogress etc at the macro level and on a per assignee basis. What we want is to create a sheet that eventually will end up in a report that for each Epic what is the status of all children issuetype. What I need to do isextract the name of the Epic on each sheet and then determine how many are in planning, inprogress, code review, validation, done. In addition, we would continue to create formulas for the number of story points, no points, unassigned etc. these would then roll into a report and we could then dashboard the results for each workstream. Creating these formulas is easy but I need to ectract out to the second sheet just the Epic names as a start to interogate each of those Epics. Thanks so much.
Bruce Johnson
Director Portfolio, Project Methods & Governance
Veolia North America
Boston, MA
-
=index(collect(summary:summary,epicyes:epicyes,contains("epic",lower(@cell))),1)
will return the summary value for the first epic. change the 1 to any value (or use a count() to automate) to return X value. (you can also switch the ranges to other sheet references to move this to a second sheet.
alternatively you can use a copy or move workflow with a helper checkbox to move all of the row information to a new sheet.
-
Hi L@123
Thank you for the reply. I like the formula and it will most likely work - BUT - the difficulty is automating the return X value.
I want to automate that but in looking a the sheet I sent, the Epic name has a variance between the Epics due to Story, Task and Sub-Task names in between those names - I tried playing around with count children and that gets complicated and I need to know where the start and end of each epic - which is variable - the epic may have 3 stories or 10 stories and each of those could have 1 task or 10 tasks - To get a consecutive unique number 1, 2, 3 for each Epic name gets complicated - so looking at the sheet previous -
Run-ANLY-VNA-Marcom-Qualtircs - has 13 children, sub-children etc.
Run-ANLY-VNA-Marcom-Hubspot - has zero
Run-Data-VNA-ENT-Tidemark - has 10 children, sub-children etc.
What I would like to end up with is a simple grid that shows the status of each Epic as such
Seems like it should be easy but it seems that it gets complicated with real world circumstances. Any help, guidance is very appreciated.
Bruce Johnson
Director Portfolio, Project Methods & Governance
Veolia North America
Boston, MA
-
Do you have a column showing the status of each epic? If so you can use a countifs
=countifs(epicyes:epicyes,contains("epic",lower(@cell)),status:status,[total backlog]$1,summary:summary,[Epic Name]@row
Looking into your naming schema a little more, it seems like you might need a helper column.
=if(count(parent(summary@row))>0,parent(summary@row),"")
which you can then use to reference for your countifs() to make sure you are referencing the correct name.
-
Thanks @L@123
yes eventually I want to interogate other columns for status and the like and I have that - I don't want to create a bunch of columns in the main sheet - I could if there is no other alternative - BUT - I wanted to see if there was a way to extract only the EPIC names to another sheet so I could have that sheet look up and countif off the main sheet - of course I could create formulas in the main then run a rport against those - but lots of people will be adding stories, tasks and sub-tasks to the epics and chances are - out of the 100's of people using these sheets - someone or many will overwrite, change, delete, screw up the sheet - which is why I want a separate sheet - sheet summary won't work - I don't know the epic names and that would have to be added for each workstream. So simply put - what is needed to create a unique name as a match for a non-contiguous column of Epic, Story, task and sub-task under IssueType - where I only want it to count if it = Epic. and given your formla from previous messgae a sheet that extracts the Epic Name under the summary column and helper columns I would expect to have to use them.
thank you very much
Bruce Johnson
Director Portfolio, Project Methods & Governance
Veolia North America
Boston, MA
-
I mean... if you do a column reference you can filter it and post values to another sheet using index(collect(),count()) then you can use other formula referencing the filtered data if you want.
-
Hi @L@123
Thanks for the reply - let's forget about index(collect - that I can now get working thanks for your help.
What is not evident is getting the Epic name to increase by 1 - so that I have a unique name - the spacing of those in the helper column is random - again - some epics have more stories, tasks, subtasks than others - so I need a way to add 1 to the previous epic name (epic1) in that column so the next Epic will be named Epic 2 - so on and so forth.
tyvm
Bruce Johnson
Director Portfolio, Project Methods & Governance
Veolia North America
Boston, MA
-
something like
=if(not(isblank(epicname@row)),"Epic"+count(epicyes$1:epicyes1),"")
in the second column, then drag down?
Or do you want to start with a special number/actually reference the previous submission
-
that did the trick - just had to start the epiceyes$1:epiceyes at the first instance of epiceyes. thanks so much for the help - really appreciated that you hung in there.
Bruce Johnson
Director Portfolio, Project Methods & Governance
Veolia North America
Boston, MA
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!