Forms and PQ - find new name and then do query functions

[davidmaddock54]

I am using a form to capture Licence data for volunteers.

I want to have staff be able to use the form to add a new volunteer but also to update a volunteer's current data.

I can see how to use PQ to do this with some work from my end using fill down and latest date, but what I can't work out how to automate is separating the names and also recognising when a new name has been added to go through the process of filtering to latest and filling down.

I have attached static version as demo. Unsure if the answer lies in PQ, or if I'm better getting the data in and then using regular formulas which I'm very rusty at.

[#2 · Accepted solution]

@davidmaddock54 Please have a look at the attached file. I added a query that does all in one process, by grouping and then sorting, filling in custom columns. Finally, keep only the relevant column, expand and clean-up.

Many of these steps could be "joined" together, but I kept the step-by-step approach. It's easier to follow what's going on that way.

[#3]

Wow. That looks like it'll work perfectly, I figured Grouping would have something to do with it, I should be able to follow those steps I think though.

Thanks!

[#4]

Thanks again for this. Working perfectly in the demo form spreadsheet.