Question

Hi there.  We have 16 geographically dispersed service locations who perform time of service collections and bank deposits.  Our finance team needs to capture and reconcile these collections & deposits on a daily basis.  I have created a form to collect time of service collection information from each location, and I'm trying to establish an automated compliance process that notifies us if there are any missing entries.  At it's core, if any of the 16 site names are not present as an entry in the REPORTING SHEET, then mark as missing.

I might need to expand to include AND statements surrounding the date of entry and such, but I simply cannot figure out where to start with regards to determining if entries are missing to begin with.  Any suggestions??

JS_NCHC · Accepted Answer

Hi again.  I figured out the core formula I needed, so I thought I'd update my post in case others have similar questions in the future.

Form for submission is part of "Daily Collections" sheet, and SITE NAME is the site submitting the form.  I created an Audit sheet with a list of SITE NAME's, and look to the Daily Collections sheet to determine if at least one submission matches with SITE NAME, resulting in "Y" or "N".

=IF(COUNTIF({Daily Collections SITE NAME}, =SITE NAME@row) > 0, "Y", "N")

Thanks! 😁