Reports- HELP


I am trying to set-up a weekly report, by coordinator, and am having trouble. Essentially, I want the report to include any row information with dates in the current week. The trouble is, each row has multiple dates and I only want to search for most recent date column by row and have only that information included in the report. I have tried many different iterations, but find myself stuck. Any help would be greatly appreciated!


  • The more I think about it, I really need to be able to create a report from a sheets' conditional formatting. I know this has already been added as an enhancement request.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you able to provide screenshots for context?

  • Hey Paul,

    I will give some more info and a screenshot. We have clients that complete surveys approximately every 4 wks from whenever their official start date happens to occur. So, each row (i.e., client) has X amount of survey dates depending on how long they have been with us.

    I would like to send a weekly, personalized, report to each coordinator (total of 4) to let them know which of their clients have upcoming (in the next week) or late surveys: i.e., cells in this sheet that are either pink (i.e., upcoming in 1 week) or red (i.e., late).

    Ideally, I would be able to have a report, by coordinator, showing any of the pink or red clients for that week and no other data.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Do you have the coordinators "assigned" to each row in a contact column?

    How do you currently have your conditional formatting set up?

  • Hello Paul,

    Yes, the coordinator name and email is associated with each row/client. The current conditional formatting is set-up for each survey timepoint (there are about 10 timepoints per client) to highlight pink if it's upcoming in the next 7 days and red if the survey is late (i.e., no completion date has been entered by coordinator).

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I would suggest inserting a checkbox column with a formula that mimics your conditional formatting logic. Then you can create a report filtered by the checkbox and then filtered by the coordinator being "Current User".

  • Thank you! I had not considered that. I have 20 conditional formatting rules at the moment (2 for each of the 10 timepoints). I have no idea how to work that into a formula for the checkbox column. Is it possible to create this as a single formula?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You would use an IF/OR combo and possibly an AND. What is your logic for one?

  • So sorry for the late reply, but I have figured it out! I appreciate all of your help- having an outside perspective helped quite a bit!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!