Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #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?

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,718
Default Making a structured reference absolute?

Price, January, February...........December are define name ranges
Price in B1
January in C1
February in D1
and so on....

=SUMPRODUCT(Price*INDIRECT(C1))
copy across

"Ted M H" wrote:

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?

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 83
Default Making a structured reference absolute?

Hi there, Teethless. Thanks for the quick reply. This is an interesting
solution, but not exactly what I'm looking for. If I understand this
correctly I have to convert my table back to a range, define the ranges and
then I can use the solution.

What I'm trying to do is to use Excel 2007's new tables / structured
references in the solution. It's as much an exercise in learning structured
references as it is to produce the number results. My original formula using
mixed cell references also solved the problem--but without structured
references.

I tried some variations on your theme--using the INDIRECT function with the
structured references, but I get the #REF error message. I'll keep trying.

"Teethless mama" wrote:

Price, January, February...........December are define name ranges
Price in B1
January in C1
February in D1
and so on....

=SUMPRODUCT(Price*INDIRECT(C1))
copy across

"Ted M H" wrote:

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?

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Making a structured reference absolute?

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?



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 83
Default Making a structured reference absolute?

Hi T. Valko,

Volatile or not, this is the solution I was looking for. My question would
be what do you mean by "That makes the formula volatile...."?

I agree that you would think that a $ character would do the trick, but as
you've observed, it doesn't.

Thanks very much for your reply.

"T. Valko" wrote:

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?






  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Making a structured reference absolute?

what do you mean by "That makes the formula volatile...."?

INDIRECT is a volatile function.

A volatile function recalculates *every time* a calculation is triggered by
some event. Normally, functions (formulas) only recalculate when their
dependencies change but volatile functions recalculate at every calculation.
This could slow things down if you have large amounts of formulas on large
amounts of data.


--
Biff
Microsoft Excel MVP


"Ted M H" wrote in message
...
Hi T. Valko,

Volatile or not, this is the solution I was looking for. My question
would
be what do you mean by "That makes the formula volatile...."?

I agree that you would think that a $ character would do the trick, but as
you've observed, it doesn't.

Thanks very much for your reply.

"T. Valko" wrote:

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?






Reply
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 10:28 PM
Making Sum ranges Absolute Ken Excel Discussion (Misc queries) 1 February 19th 05 07:47 PM


All times are GMT +1. The time now is 09:34 PM.

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

About Us

"It's about Microsoft Excel"