SUMPRODUCT with multiple criteria and dynamic sum-column

Guys I'm kinda stucked here. The following formula that I have on workbook A, pulling data from workbook B ('[EXAMPLE.xls]Dec)

Please Login or Register  to view this content.

The first two arguments I think are self explanatory. The third one ('[EXAMPLE.xls]Dec'!$D$5:$D$1000) however, I would like to be dynamic. You see, on D4 of workbook B there is heading, say, YIELD, on E4: AVERAGE, on F4: ACTUAL, on G4: TOTAL.

Then on Workbook A, cell A1, I have validation list containing those values on D4:G4 of workbook B. So I would like the above formula to change the range of D5:D1000 to E4:E1000 if A1=AVERAGE, or to F4:F1000 is A1=ACTUAL and so forth... However, I cannot use offset as my workbook may be closed sometimes... What can I use guys?

Thanks

I suspect you would get away with changing:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.