Seems there should be a better way but this will work:

=SUMPRODUCT(INDIRECT("Forecast[Price]"),Forecast[January])

That makes the formula volatile which is a big negative in my opinion for
something seemingly as simple as wanting to make a reference absolute.

You would think that the standard $ would be used to make the reference
absolute. Something like this:

=SUMPRODUCT(Forecast[$Price],Forecast[January])

But, this is not the case!


--
Biff
Microsoft Excel MVP


"Ted M H" wrote in message
...
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,D2:D100 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?