I have a workbook with two google sheets.
The first sheet "Data" has data added daily.
Columns: Date, LastName, FirstName, Worked Dept, Worked Job, Hours, FullName
The second sheet "Static List" is a static list of employees.
Columns: FullName, FirstName, Email Account / Supervisor, Time Entry, =Today()-1, Recipient, Email Sent
The goal (which I cannot figure out) is to have a formula (Vlookup, Match, IF, IFS, etc) or an Apps Script to do the following.
I need to use the "Static List Sheet" LastName column and the =Today()-1 column to check to check the "Data Sheet", which is updated daily, to see which LastName(s) do not have an entry for the previous day and report "Yes" or "No" to Time Entry column. Then if Time Entry column has a "No" I need to copy the Email Account / Supervisor column to the "Recipient" column.
I have not been able to make any formula return the correct findings for the date in the "Data Sheet" compared to the date in "Static List Sheet"
I got this to work but I had to set the Column Value and the column value changes everyday and if I use date versus LastName then it returns "Found" if the date found is give or take 1 day.
=IF(ISNA(MATCH(A2,'Data'!$G$760:$G$818,0)),"Not found","Found")
If an Apps Script would work better, please let me know and if you have any ideas on how to write the script.
I plan to use this in conjunction with a MailMerge Apps Script I found and have working.
This is the workbook link
https://docs.google.com/spreadsheets/d/17lmyoFA0yYY2HjGpyIviKNwPTBAFTDgq2Ctg0gO8hBs/edit?usp=sharing
Any Help would be greatly and wholeheartedly appreciated
Kurt
LinkedIn
Twitter
