Making a structured reference absolute?
in [Functions]
|
From: Ted M H on 13 Apr 2008 11:15 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?
From: Teethless mama on 13 Apr 2008 11:59 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? >
From: Ted M H on 13 Apr 2008 15:33 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? > >
From: T. Valko on 13 Apr 2008 18:10 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" <TedMH(a)discussions.microsoft.com> wrote in message news:CFDCE717-C105-4133-878F-23B880E15BEF(a)microsoft.com... > 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? >
From: Ted M H on 13 Apr 2008 22:31 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" <TedMH(a)discussions.microsoft.com> wrote in message > news:CFDCE717-C105-4133-878F-23B880E15BEF(a)microsoft.com... > > 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? > > > > >
|
Next
|
Last
Pages: 1 2 Prev: Using Bloomberg's BDP function in VBA Next: Multiple condition countif for excel 2003 |