Formula to update value when newer dates are added

I have a master data sheet where all of my formulas and data live. This sheet feeds into our financial dashboard and I'd like the formulas to be working efficiently so I don't have to go in and constantly update them.

I want to create a formula in my master data sheet that searches for the most recent date in my AR Tracking sheet and returns the corresponding Actual Percent value on the master data sheet. I have tried a few combinations of INDEX, MATCH, MAX but I cannot get it right. I want to create this formula once and it automatically updates anytime I update the AR Tracking sheet. Is this possible?

The snippet below is the AR Tracking sheet.


Thanks!

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @Jen Scott 

    Hope you are fine, please try the following formula:

    1- Recent Total AR =VALUE(JOIN(COLLECT([Total AR]:[Total AR], Date:Date, MAX(Date:Date))))

    2- Recent Percent =VALUE(JOIN(COLLECT([Actual Percent]:[Actual Percent], Date:Date, MAX(Date:Date))))

    the following screenshot show the sample:


    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!