Formula to update value when newer dates are added

Jen Scott

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!

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: