Grabbing text from a cell based on the "current" or recent date

Luis Perez

Hello All,

I am trying to figure out how to grab a specific calc from a sheet that I will use as a KPI on a dashboard via the metric widget. I need to create a formula that will go through and find the calculation that is the most current and place it in the cell I will use for the metric. The idea is that as I populate the sheet it will automatically change the metric data based on the date and update the dashboard.

In the sheet below I want the most current % (Spent/Projection) to be pulled into the (Current) Spent/Projected column.

Thanks!

Paul Newcome

I would suggest this...

=INDEX([Spent/Projection]:[Spent/Projection], MATCH(MAX(COLLECT([Month/Date]:[Month/Date], [Spent Committed]:[Spent Committed], @cell <> "")), [Month/Date]:[Month/Date], 0))

Bassam Khalil

Hi @Luis Perez

Hope you are fine, please try the following formula ( you need to use the same columns name i used )

=IFERROR(VALUE(JOIN(COLLECT([Spent/Projection]:[Spent/Projection], [Month/Date]:[Month/Date],
 MAX([Month/Date]:[Month/Date])))), "") 

the following screenshot shows the result:

Luis Perez

Thanks Bassam,

This worked great but I forgot to add one bit of information.

I want to pull the "Spent Projection" from the cell that matches the "Spent Committed" with the last known entry. Basically ignore any blank "Spent Committed" cell.

See row 24.

Thanks!!

Paul Newcome

I would suggest this...

=INDEX([Spent/Projection]:[Spent/Projection], MATCH(MAX(COLLECT([Month/Date]:[Month/Date], [Spent Committed]:[Spent Committed], @cell <> "")), [Month/Date]:[Month/Date], 0))

Luis Perez

That Worked! thanks!

Paul Newcome

Happy to help. 👍️