Can you reference the primary column with comments when exporting a report?

Ross Novotny
Ross Novotny ✭✭✭✭✭✭

When you export a report into excel that has comments associated with rows, those comments show up on a separate tab in Excel. But the "Comments" tab only references a row number, which isn't that helpful when these are used as quick, high level reports. Having to try to align comments based on row numbers and flip back and forth between tabs isn't very realistic. It would be very helpful if you could have the primary column populate next to the comment that was associated with it so that a person reading the comments would have the proper context.

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Ross Novotny

    As a possible workaround, you could maybe use a VLOOKUP to match them together.

    Would that work/help?

    I hope that helps!

    Have a fantastic week & Happy Holidays!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Ross Novotny
    Ross Novotny ✭✭✭✭✭✭

    Hey Andree -

    I don't think that would work. Because the Vlookup would have to be in the body of the comment, which is not possible that I know of. At this point the only thing that would work would be to reference the primary column (In this case a company name) in each comment, which seems redundant.

  • Ayelet Weiner
    Ayelet Weiner ✭✭✭✭✭✭
    edited 12/30/20

    I believe others have voiced similar frustrations with exporting to Excel. I recommend submitting an enhancement request.

    In Excel, I created a hyperlink with the match function as a temporary solution to quickly navigate to the comments tab. If you pull the same report on a regular basis, you can record a macro so you don't have to always do the below steps manually.

    On your exported report, in a blank column on the first tab, type "Row 1" in the cell on the second row. Then drag it all the way down. So you should have Row 1, Row 2, Row 3, etc. That's how you're going to match the row on the first tab to the comments tab.

    Next, in another blank column, enter the following formula:

    =HYPERLINK("[Name of your file.xlsx]'Comments'!A"&MATCH(K2,Comments!A:A,0), "Comments")

    (K2,Comments!A:A,0) - Replace the K2 in this part of the formula with the cell location you entered "Row 1" on. (i.e. if the column you used to type "Row 1" is in Column G, then your formula would reference G2)

    Now you should have a hyperlink with the words "Comments" that should bring you right to the comments on the second tab. Just drag the formula all the way down.


    Hope that helps.

  • Ross Novotny
    Ross Novotny ✭✭✭✭✭✭

    Thanks Ayelet - Thats a nice workaround. Unfortunately this is for a client of mine, I am not reading or exporting the reports myself. I will submit a feature enhancement, it seems like the Excel export is a not well thought out execution in regards to the comments section.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Ross Novotny

    Ayelet's solution was what I had in mind, but another option could be to use Zapier and trigger a copy of each row's comments to a so-called helper column. (untested)

    If possible, would that be an option?

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Ross Novotny
    Ross Novotny ✭✭✭✭✭✭

    @Andrée Starå I think I am understanding the suggestion. Is the idea that I trigger a zap when a new comment is added and have that comment show up within the sheet, that will be then found in the report and shown when automatically sent? That may work. It is a little complicated because this sheet then transfers to another sheet at a certain stage. So I think I will have to add a "comments" column and then hide it at every step of the process.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Ross Novotny

    Yes, exactly!

    How did it go?

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Ross Novotny
    Ross Novotny ✭✭✭✭✭✭

    @Andrée Starå My client didn't want to go with this solution as I explained how it would play out and how it wasn't as strong as we've all envisioned. I know it would work, but didn't implement it with the client.