Prev: Using Bloomberg's BDP function in VBA
Next: Multiple condition countif for excel 2003
From: T. Valko on 13 Apr 2008 23:13
>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" <TedMH(a)discussions.microsoft.com> wrote in message
news:58297397-14F1-40E8-94ED-1B3D6E2A1EFE(a)microsoft.com...
> 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?
>> >
>>
>>
>>


First  |  Prev  | 
Pages: 1 2
Prev: Using Bloomberg's BDP function in VBA
Next: Multiple condition countif for excel 2003