Help Creating a way to track supervision visits

[KRobOT17]

Its been too long since my statistics and microsoft office course in college! I need help creating a way to track supervision visits in Excel. As a supervisor, I have to see the client every 5th visit OR every 60 days, the other times my assistant can see them. I just cannot figure out how to input this into excel. I need each child, who saw them (me or my assistant) the dates, and then some way to track if I saw them within 5 visits or within 60 days. Anyone have any ideas? I appreciate any help! Thank you!

[#2]

@KRobOT17 

A) Create a simple database with one row per visit, use these column headers for the table

Date     ChildID    ChildName  Visited_by

Once that's done and maintained with a record of each visit, it would be fairly straightforward to create a "dashboard" sheet that shows for each child whether or not it's time for a mandatory visit by you. Statistics has nothing to offer here.

I'm attaching a sample. This sample file does use the UNIQUE and FILTER functions, both of which require the most current version of Excel.

It's not the most elegant, perhaps, but it works. Could be enhanced, but let's first get your response to see if it does what you want. Play around with the names...the dates. Right now it highlights if the designated person (Walt) is in need of doing the next visit by showing the last date and highlighting with the word "True" that the 60 days has been exceeded. There's also a count of how many visits by the other person (Michael) since Walt's last visit.

The heart of this, though, is a solid and consistently maintained database.

[#3]

@mathetes

Yes I think this will work, but on the second sheet, why is it giving the #NAME? error? How do I fix that?

[#4]

Could you tell me where (which cell(s)) are giving that error?

Do you have the most recent version of Excel? There are some functions in this solution (UNIQUE and FILTER) that require the most recent version. 

In the meantime, here's a slightly revised version which, if it's working on your system as it is on mine, uses conditional formatting to highlight the clients where your visit is most important.

[#5]

hmm, yesterday those names were giving the error, but today it is fixed.. not sure how but, thanks!

[#6]

Let me know if any questions remain, or if you'd like more enhancements.