Nested formula to present the most recent KPI value from a row

ACB

I am trying to pull the most recent month's value to the 'Current Month Data' cell to use for Dashboard Widgets that will only present the Goal and Current Month Data. There are 30+ KPI values that need to have the latest month's value, and I'm stumped on how to build the nested formula to make it work. The same formula will work for every KPI (row).

Below you'll see the month columns, and the Current Month Date column in the screenshot. I thought I'd just use the modified column with a between dates formula. For example: the value in the Current month data column for January is displayed if the modified column has a date between Feb 1 and Feb 28, then the next imbedded piece is the same for Feb, Mar, April, etc. Any ideas would be greatly appreciated.

Paul McGuinness

Hi @ACB

Two options here:

1: You can either restructure your sheet to run months in a single column and your KPIs go into separate columns, that will allow you to use straightforward date formulas to float certain row data to the top in your modified column.

2: Slightly more difficult to get going and requires a few separate formulas around the sheet but this also works and I've used in a few situations.

You will require a couple of new header rows,

Top row has this formula and works to identify which column to pull each month, it works by comparing the month from todays date to the number in the second row, numbers in the second row are fixed and manually entered.

=IF(MONTH(TODAY()) = [Aug actual]2, "Pull", "")

You also need a 2nd KPI name column to run the match function (I assume your KPIs have names just not shown in your screenshot), you cant incorporate the existing name column in the formula as you end up with a circular reference. you can then use this formula to pull the corresponding row data from that months column

=VLOOKUP([KPI Name]@row, $KPI$1:$[Aug actual]$8, MATCH("Pull", $KPI$1:$[Aug actual]$8, 0), false)

You can clean up what people see by using a report to hide the additional columns or rows.

Hope that helps

Paul