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

Answers

  • Hi @Bruce Johnson

    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

  • Bruce Johnson
    Bruce Johnson ✭✭✭✭

    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

  • Bruce Johnson
    Bruce Johnson ✭✭✭✭

    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

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 09/21/20

    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.

  • Bruce Johnson
    Bruce Johnson ✭✭✭✭

    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

  • L_123
    L_123 ✭✭✭✭✭✭

    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.

  • Bruce Johnson
    Bruce Johnson ✭✭✭✭

    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

  • Bruce Johnson
    Bruce Johnson ✭✭✭✭

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!