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

Options
Msevela
Msevela ✭✭
edited 07/12/22 in Formulas and Functions

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.

Best Answer

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    edited 07/12/22 Answer ✓
    Options

    @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.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!