Reports- HELP

jl.furstenberg

Hello,

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!

jl.furstenberg

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

Are you able to provide screenshots for context?

jl.furstenberg

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

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

How do you currently have your conditional formatting set up?

jl.furstenberg

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

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".

jl.furstenberg

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

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

jl.furstenberg

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!