View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ted M H Ted M H is offline
external usenet poster
 
Posts: 83
Default Making a structured reference absolute?

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?