Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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? |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Making cell reference absolute makes cell format text | Excel Worksheet Functions | |||
What is the shortcut key for making a cell reference absolute? | Charts and Charting in Excel | |||
Making multiple cells absolute at once | Excel Discussion (Misc queries) | |||
Trouble with making a 3D reference absolute | Excel Discussion (Misc queries) | |||
Making Sum ranges Absolute | Excel Discussion (Misc queries) |