Issue log linked to requirements on a different sheet



I have searched and searched but can't find an answer, but I can't believe this hasn't been asked before so must be doing something wrong.

I am running a post development support project where we have multiple regular releases. We gather requirements from the client develop them test them, resolve issues and release. Nothing magical about it. However, tracking it all is a bit of a 'mare as there are a lot of small changes.

Therefore I'd like to create a master requirements sheet, with all the traceability about the status of the functionality in the process and history of where the requirement came from. I can keep all records and information about the requirement at that level.

However when it comes to testing I would like a separate issue log, where the issues can be linked back to the requirement in the requirements sheet. Issues have a different set of information associated with them so need to keep them separate from the requirements sheet.

I want the client and developers to use smartsheet to collaborate on the requirements and issues, not just me updating it, so the connectivity has to be there and automated otherwise it will get out of sync and they will lose interest.

I don't seem to be able to build this one to many relationship and linkage of information between two sheets. Is there some functionality that I am missing, or has anyone resolved this issue in a different way?



  • James Keuning
    James Keuning ✭✭✭✭✭

    If each sheet has a unique identifier, you can use a cross sheet reference, probably INDEX and MATCH, to bring in info from the other sheet.

    So in the Requirements sheet I have



    etc, with details in columns.

    Then in the Issue Log I have




    I can put Req01 in my Issue Log, and use Index and Match to pull in the Req01 details from the Requirements sheet.

    I can also use Collect and Join in the Requirements sheet to bring in all of the Issues that reference the relevant Requirement. So if Issue ID001 and Issue ID023 both reference Req02, I can put those in a field in the Req02 row in the Requirements sheet so it looks like this: "ID001; ID023"

    Obviously, it helps to have your Issues and Requirements available as dropdown choices in each foreign sheet, and you can use Data Shuttle for that, but it's a premium app.

    You can add logic to the cross-sheet references so that you are only pulling active Issues/Requirements or other filtering criteria.

  • nbiswas

    I am working on a similar problem where I have to link two different form of Business Requirement Sheet with different information in both sheets. However, each have their unique identifiers. I will use the above guidelines to build Traceability Matrix.

    Any additional tips would be highly appreciated. I do have a Smartsheet Premium account.