How to have a report exclude certain items

Jackie Callahan · 06/19/20

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

Paul Newcome · 06/19/20

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

Andrée Starå · 06/20/20

Hi @Jackie Callahan

To add.

More info:

https://help.smartsheet.com/articles/522214-creating-reports

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!

Jackie Callahan · 06/20/20

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å · 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?

Jackie Callahan · 06/22/20

https://community.smartsheet.com/discussion/comment/254331#Comment_254331

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 Newcome · 06/22/20

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å · 06/23/20

@Jackie Callahan

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

Jackie Callahan · 06/23/20

https://community.smartsheet.com/discussion/comment/254542#Comment_254542

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 Newcome · 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.

Jackie Callahan · 06/23/20

https://community.smartsheet.com/discussion/comment/254562#Comment_254562

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

Paul Newcome · 06/24/20

Happy to help! 👍️

Andrée Starå · 06/24/20

@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

How to have a report exclude certain items

Answers

Categories