Excel 2010 Not Calculating Cells With User-defined Formula

[ld_pvl]

I stumbled upon a simple but yet a strange problem. I have made a small working sample that reproduces the problem.

So in the spreadsheet, I have two tables, Range1 and Range2. I also have a module with a function called

Please Login or Register  to view this content.

.

Range1 contains a column named 'added quantity' in which the cells are calculated using the 'dummy' function. The 'dummy' function just looks for the 'multiplier' that corresponds to the 'multiplierID' in 'Range2', and multiplies it to the 'quantity' and returns the product.

The problem now is this: I was expecting that if I change the value of a multiplier in Range2, the corresponding dependent cells in Range1 should change but they don't, even if I hit calculate. The cells only change if I re-commit the formula, meaning if I choose the cell and re-enter the same formula. Even worse, even if I apply the formula to all cells, the cells don't update, I either have to re-enter the formula for each individual cells or do the old-fashioned dragging.

I have attached the workbook in this thread.

Please note: I know that I could have written the function straight into the cells which would have made it work but here I'm just trying to represent my problem in a simple way, as in my original workbook the function contents are 'slightly' more complex.

Many thanks for your help,

[MarvinP]

Hi ld_pvl and welcome to the forum.

You need to read and understand http://www.decisionmodels.com/calcsecretsj.htm#nocalc

Scroll down the above page and look for the Application.Volatile line. I tihnk this will help.

[ld_pvl]

Thank you! Forgot about volatility. That solved my problem.

[DonkeyOte]

Whenever possible avoid Volatile UDFs

In this instance you would pass variables as Ranges inclusive of 2nd Table such that all cells become "precedent" cells of the function.
This means that when those precedent cells alter they will flag the UDF as requiring recalc. - as per a normal formula.

Of course, in reality, there is no need for a UDF at all:

Please Login or Register  to view this content.

[MarvinP]

Hey DO,

I'm having trouble finding the "@" notation explaination. I've got the Evaluate is the same as "[ ]", but where does @ come in. Can you point me to a web page that expains this?

Also in your formula above, why doens't "=[Range1[initial quantity]]" work as well as "=[@[initial quantity]]"

[DonkeyOte]

This is not an Evaluate method - we're using TABLE object syntax.

where does @ come in

The @ refers to ThisRow of TABLE object
(when you type a formula into a TABLE you will see the various syntax options)

If you refer to the TABLE explicitly (assumed relative otherwise) then you would not encase the TABLE name within [ ], ie:

Please Login or Register  to view this content.

Though of course if you add the above within the Range1 TABLE it will dispense with the reference.

[MarvinP]

Can you be more specific on this

(when you type a formula into a TABLE you will see the various syntax options)

I must be dense or having a bad learning day.
Keystroke level? How do I see all those options?

This is close but no "@" http://office.microsoft.com/en-us/ex...010155686.aspx

I found it at http://www.jkp-ads.com/articles/Excel2007Tables.asp
where the claim is the "@" was new to 2010 Excel.
Search for "Excel Tables This Row"

Time to go study some more!