Performance Rollup

The status is the color?

I think I got this. I had to do a bit of reverse engineering

First of all, I converted the range on Sheet1 into an Excel table. The reason for this is that among tables other benefits, tables know how big they are so you don't have to go on extending formulas or changing them as you add or delete rows from the table. See this wiki to get started with tables: http://www.utteraccess.com/wiki/Tables_in_Excel.

Tables need unique headers and I supplied them in roe 4. You can hide this row if you desire.

I did some calculations in Columns T:Y to determine which quarter is the latest with data. Columns T:W just get a sum of the actual values of the appropriate quarters and is true if the total is greater than zero.

The formula in cell X2 is a bit esoteric: =MAX(($T$2:$W$2=TRUE)*COLUMN($T$2:$W$2))-19 . This is an array formula and it gets the column number of the last occurrence of the value TRUE in the range. This happens in Column U (which is the 21st column) so that's the reason for the -19. In other words, Quarter 2. Next quarter should match column V.

Then I compute an offset. The Actuals column for quarter 1 is three columns to the right of the strategic link (SL) column, for quarter 2, it's six columns, etc.

You can highlight this range of cells an CUT and paste it onto another sheet and hide that sheet if you want.

Next I defined a named range. Normally this is done with the offset command, but in this case it's defined in terms of the table: StratLink =Table2[Strategic Link '#]. Here is another wiki on named ranges: http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges.

I have two more named ranges:
Qtr_Status =OFFSET(StratLink,0,Sheet1!$Y$2+1)
QtrActual =OFFSET(StratLink,0,Sheet1!$Y$2)

Qtr_Actual is the same column and rows as StratLink only shifted down by zero rows and to the right by whatever value is in cell Y2. In this case the Q2 Actual column.
Qtr_Status is the same idea only one column further right.

Now we go to sheet 2. I could not both honor your color coding (based on conditional formatting) AND put in the averages in the same cells. So First I wiped out the data validation and conditional formatting and changed the font to something readable.

Then, in column B, I entered in the formula: =IFERROR(AVERAGEIFS(QtrActual,StratLink,$A4),"") . In other words, get the average of the actual values for the indicated strategic link.

In column C, I put the formula: =IFERROR(INDEX(Qtr_Status,MATCH($A4,Table2[Strategic Link '#],0),1),"No Data") - this looks up the status entered for the strategic link for the quarter.

Then I reapplied conditional formatting using Use a Formula to shade the cells. Column C can be hidden.

I think that's it.

The spreadsheet I sent back to you worked by detecting the last quarter that had data in it. What do you want to base the actions on? There isn't enough data to infer anything. Also it would help if you had a mock-up of what you would like the result to be.