Returning Indexed Values of Each Row for Latest Date

Options

Hello, I am having trouble trying to return Current Value for each row corresponding to each name and for the latest date.

Attached is a simplified table:


Instead of manually inputting the 'Current Value', I would like to create a formula to return the latest cumulative value for each name row. I have tried using INDEX with MATCH and MAX but could not get it working for these two criteria (match name and max date). Please help and let me know whether this is doable. Thanks!


PS: To derive Cumulative Value I used the formula [Transaction Value]@row + SUM (ANCESTORS())

Best Answer

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    Hi @Iva

    Hope you are fine, you need to add a helper column call it "Max Value", the following is the formulas:

    1- Max Value = MAX([Transaction Value]@row:[Cumulative Value]@row) convert it to column formula

    2- Current Value =MAX(COLLECT([Max Value]:[Max Value], Name:Name, Name@row)) convert it to column formula

    the following screenshot shows the result


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Iva
    Iva ✭✭
    Options

    Hello @Bassam.M Khalil

    Thank you for your reply. I tried it on my actual worksheet, by using helper column and MAX with COLLECT the value to be picked up would be the biggest one (instead of the value with the latest date). When there is a negative value unfortunately this method does not work.

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    @Iva

    As i understand from the screenshot in your post that you need the max Value of Transaction Value & Cumulative Value. check the screenshot in your post, and please post a sample show the value you need manually and i will try to write the formula for you

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Iva
    Iva ✭✭
    Options

    @Bassam.M Khalil

    Please refer to my original post again, the highlighted part in my screenshot is the one that I asked (Current Value, not Cumulative Value); my aim is to show current value for each row corresponding to each name.

  • Iva
    Iva ✭✭
    Answer ✓
    Options

    Update: I managed to get this solved by using SUMIFS instead 😀

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    @Iva

    Perfect 👍️

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!