T
Ted M H
Excel 2007 table is a forecast with product name in col A, unit price in Col
B and forecast units by month in col's C:N. I want to show forecast dollars
in each month's column. This formula outside the table (cell C102) works
fine:
=SUMPRODUCT($B2:$B100,C2:C100)
Since I anchor the Unit price column reference, I can copy the formula
across for the remaining 11 months (=SUMPRODUCT($B2:$B100,D2100 and
=SUMPRODUCT($B2:$B100,E2:E100 and so forth).
But I want to use structured references to the table to solve the problem.
I enter this formula in cell C102:
=SUMPRODUCT(Forecast[Price]*Forecast[January])
The formula works fine, returning the same result as the formula using cell
references above. The problem is that when I copy the structured reference
formula, both Price and January autofill/extend as if I am using relative
cell references. That's what I want for the month (January, February, March,
etc.), but I want to anchor the Price column in the formula.
How do I make the structured reference to Forecast[Price] absolute while
leaving the reference to Forecast[January] relative?
B and forecast units by month in col's C:N. I want to show forecast dollars
in each month's column. This formula outside the table (cell C102) works
fine:
=SUMPRODUCT($B2:$B100,C2:C100)
Since I anchor the Unit price column reference, I can copy the formula
across for the remaining 11 months (=SUMPRODUCT($B2:$B100,D2100 and
=SUMPRODUCT($B2:$B100,E2:E100 and so forth).
But I want to use structured references to the table to solve the problem.
I enter this formula in cell C102:
=SUMPRODUCT(Forecast[Price]*Forecast[January])
The formula works fine, returning the same result as the formula using cell
references above. The problem is that when I copy the structured reference
formula, both Price and January autofill/extend as if I am using relative
cell references. That's what I want for the month (January, February, March,
etc.), but I want to anchor the Price column in the formula.
How do I make the structured reference to Forecast[Price] absolute while
leaving the reference to Forecast[January] relative?