help to fill the table

[reda]

Please help on the attched file' i need to fill the small table on the right with formula
Kindly note that the formula should search the table for the zone name and column header.
Please note also that the location of the cell is changing.
So i need a formula that works regardless of the location of thr cells

[Leith Ross]

Hello reda,

Formulas do not work well with merged cells. To work around the problem, I have used column "F" as a helper column. This is also a dynamic named range "Zones". Columns "D" and "E" are also dynamic named ranges: ACHVD_TODATE and TARGET, respectively. You can hide column "F" if you want to.

Here are the formulas for the total table in "G8:K10". The attached workbook has all the changes made.

H9 =SUMPRODUCT(ACHVD_TODATE,--(Zones=H$8))
I9 =SUMPRODUCT(ACHVD_TODATE,--(Zones=I$8))
J9 =SUMPRODUCT(ACHVD_TODATE,--(Zones=J$8))
K9 =SUMPRODUCT(ACHVD_TODATE,--(Zones=K$8))

H10 ==SUMPRODUCT(TARGET,--(Zones=H$8))
I10 =SUMPRODUCT(TARGET,--(Zones=I$8))
J10 =SUMPRODUCT(TARGET,--(Zones=J$8))
K10 =SUMPRODUCT(Target,--(Zones=K$8))

[reda]

Thank you
But how you made the dynamic ranges

[mdmackillop]

But how you made the dynamic ranges
Look in Formulas/Name Manager for the formulae