Formula for first/last value based on year.

Brandon22

I have a large sheet where employees completed inventory checks every week over the last few years. Trying to figure out a formula to get the first and last entry based on a cell that contains last years "year". This is to gather our yearly starting inventory and yearly ending inventory.

This is the formula I have to get the last quantity entered on the sheet but I cannot figure out how to incorporate the cell with last year as a criteria.

=IFERROR(INDEX({Item 1}, MATCH(MAX(COLLECT({DATE}, {Item 1}, @cell <> "")), {DATE}, 0)), "")

Darren Mullen

@Brandon22 You'd need to have the year on every row, alternatively, if you have the date on every row you could check the year of the date with the YEAR() funciton.

Brandon22

Hi @Darren Mullen thank you for your help but I don't fully understand. I want to have the formulas on each row connected to a date I can change at the top of the sheet (under last year) to collect the first and last entries of whichever year I enter there.

So basically in the cell intersecting Item 1 and Yearly Ending Inventory, I would want it to populate "132" as that was the last entry in my reference sheet for the year 2022.

Would I be able to do this with modifying a simpler formula such as:

=MIN(COLLECT({Item 1}, {Created}, [Daily Use]2))

Darren Mullen

@Brandon22 You would need some more complex than that. What you proposed would give you the minimum item number. It would take some thought to come up with something.