Formula to get most recent status for a particular client
This is a repost of a question I ended up solving myself, but have run into another issue. Here's the setup:
I have a "History" sheet that logs the results of form submissions:
I have a second sheet, the "Summary" that should look something like the below image, showing the status of each category from the most recent survey form:
You'll notice that the status summary is incorrect for Client 2 and Client 3, because my formula is grabbing the max date, and when forms are submitted on the same day, this will have duplicates. Here's the formula:
This formula worked great when the "Date Created" in the history column was an auto-numbered "Created" field, because the entries had timestamps, but it's been requested that the forms allow for manual date entry.
In addition to trying to make this work again with a manual date, I'd also like to present the SECOND most recent survey result. So I'd have "Current Client Satisfaction" and the next column would be "Previous Client Satisfaction". I think I can do that with LARGE(), but the formula in it's current incarnation needs a fix before I can move on to this problem.
Help Article Resources
Check out the Formula Handbook template!