Get the latest value, based on a date, from a specific value.

Msevela

I know that i will probably have to use Index, Max and Collect. I Just don't know how.

This is the "Registry" table which is going to display the last value, based on the last date, based on the product SKU. Only with "In"s.

And This is the "Movements table" which logs all the data.

(This structure has the objective to record the last price wich the material was purchased.)

As i've said, i tried using Max Collect Index, but with no success.

Mike TV

@Msevela

Ok. Swear I've got it this time.

First you need to create a helper column on the target sheet to give you the max date of an "In" on a SKU. Example:

Max Date formula (format column as a date column):

=MAX(COLLECT({Created}, {In/Out}, ="In", {SKU}, =SKU@row))

Price formula:

=MAX(COLLECT({Price}, {SKU}, =SKU@row, {In/Out}, ="In", {Created}, =[Max Date]@row))

That should do it.