LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
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?

 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Making cell reference absolute makes cell format text Excel Worksheet Functions 2 September 22nd 06 04:47 PM
What is the shortcut key for making a cell reference absolute? Dell Charts and Charting in Excel 1 September 20th 06 08:46 AM
Making multiple cells absolute at once Jamie A Miller Excel Discussion (Misc queries) 2 May 25th 05 01:13 AM
Trouble with making a 3D reference absolute Sherry Excel Discussion (Misc queries) 1 March 24th 05 09:28 PM
Making Sum ranges Absolute Ken Excel Discussion (Misc queries) 1 February 19th 05 06:47 PM


All times are GMT +1. The time now is 04:09 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"