How to have a report exclude certain items

06/19/20
Answered - Pending Review

Hi,

I'm trying to build an action item report that only shows approved items that do not meet certain criteria. So in the smartsheet, there is a check box for a document attached and several columns for links to other places.

I would like to have the report include only items that are marked as approved (a dropdown column) but do not have a checkbox checked and the cells in the other three columns (where the links are added) are blank.

Is there a way to do this?

Thank you!

Jackie

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Yes. You would set these rules up in the Report Builder.

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

    Hi @Jackie Callahan

    To add.

    More info:

    I hope that helps!

    Be safe and have a fantastic weekend!

    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 PARTNER & CONSULTANT / EXPERT

    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.

  • Thank you both. However, what I cannot figure out is how to build in the logic I outline above. Below is a photo of what I have. I think that means it will only include a row if none of those items are true. But I need it to include the row if any one of those items is not true. Our goal is to make sure all of those items are added. If any one of them are not, it should include the row - is that possible?

    Sorry, my original question was not clear - thanks!



  • Andrée StaråAndrée Starå ✭✭✭✭✭
    edited 06/20/20

    @Jackie Callahan

    Happy to help!

    If I understand you correctly, you could try clicking on the and so it changes to an or.

    Would that work?

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    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.

  • Hello!

    Well it would if I could change everything but the first one. So it needs to be if it is marked approved and any one of the following links is missing or quote is not checked. So if only I could change everything but that first one!



  • Paul NewcomePaul Newcome ✭✭✭✭✭

    I would suggest a "helper" column (that can be hidden after setup to keep the sheet looking clean) such as a checkbox type where you can enter a formula to automatically check the box on any rows meeting that criteria. Then you can build your report based on that helper column.

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

    @Jackie Callahan

    Yes, i agree with Paul. Helper column is the way to go.

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    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.

  • Ok, that makes sense. So I assume the helper column will be a checkbox? And I would need to write a formula to check the box if the criteria is met?

    For the formula, essentially, if column {Final Approved? dropdown with two options - approved and approved, gang print} is not blank and any one of the following items is not true, I need the helper column to be checked:

    • Print quote attached? Checkbox (needs to be checked)
    • Link to approved PDF in BrandFolder (needs to have text within)
    • Link to print ready file in BrandFolder ( needs to have text within)
    • Link to Web Viewing version PDF in BrandFolder (needs to have text within)
    • Link to file on Roseburg.com (needs to have text within)

    The point of this report is that we are tracking approval of literature through a smartsheet and once something is approved, we need to make sure the print quote is attached and all the links are added. We want the report to be action items - i.e. show what we are missing.


    I hope this is clear. This seems like a complicated formula for the helper column so any help would be much appreciated! If you can see a better way to do this too I am certainly to any advice - I really appreciate your guys' help!


    Jackie

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    edited 06/23/20

    The formula for the helper column would look something along the lines of...

    =IF([Final Approved?]@row <> "", IF(OR([Print Quote Attached?]@row <> 1, [Link To Approved PDF]@row = "", [Link to print ready file]@row = "", [Link to web viewing]@row = "", [Link to file on Roseburg.com]@row = ""), 1))


    Just update your column names to reflect what you have in your sheet, and you should be good to go.

  • That worked! Thank you so much for your help!! Much appreciated.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help! 👍️

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

    @Jackie Callahan

    You're more than welcome!

    I saw that Paul answered already!

    Let me know if I can help with anything else!

    Best, 

    Andrée 

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    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.

Sign In or Register to comment.