Collect most recent date from another sheet based on certain criteria


I have a fleet maintenance log that has invoices per vehicle with a column that is a drop-down with multiple values based on the service provided.

I am trying to make a sheet that would be a quick reference to see the most recent service per vehicle per category.

Being multiple services will but under a category I planned on doing a formula with CONTAINS and listing all services I want to be under that category. Before I tried listing all of the services I tried the formula =MAX(COLLECT({date}, {erac unit}, [Unit Number]@row, {service ROW}, CONTAINS("Brake Pad - Front", {service ROW}))) Just to pull the most recent date for the service Brake Pad - Front but it is returning with 0 for all cells and not the date. If I could figure out the formula I would have the following service categorized as Breaks

Do I need to add a helper column on the Maintenance Log that has the category? But if I did that I will still have some that would need to have multiple categories as on one invoice I could have Breaks and Oil changes.

I am open to anything! I really just need something easy for my team to glance at and see exactly when an oil change or breaks (just an example as i will probably add more categories once I finally figure it out) was done last!

Thank you in advance!!!

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!