Is there any way to determine what is triggering a name to recalculate?

[foxguy]

I'm trying to get rid of all the volatile Dynamic Range Names in my workbook. IOW: I'm trying to get rid of "Offset(....)" in my Range Names.

I have a Range Name that uses a UDF() 2 times. I have verified that the UDF() has "Application.Volatile False" as it's first line. The UDF() is being executed hundreds of times when I move my cursor.

Is there some way to determine what cells are triggering the Range Name to recalculate?
Or maybe an explanation of why the UDF() is being triggered when all I do is move my cursor?

Here are the UDF() and Range Names. $A$13 and $A$15 have #s in them (no formulas).

Please Login or Register  to view this content.

[tlafferty]

Hmmm...could it be the reference to .EntireColumn?

[foxguy]

What are you thinking would be the reason that a reference to the EntireColumn in a UDF() cause the RangeName to be volatile.

[tlafferty]

Just going out on a limb. If there are volatile cells in that column, would that not be enough to trigger recalc?

[foxguy]

There are no formulas in that column. It is raw data.

I believe that the Range Name is being recalculated because some other cell or Name uses a formula that uses that Range Name. I'm trying to find that cell or name. This particular Range Name will be easy to find, but I want to do this for hundreds of Range Names, so I need to figure out what is triggering this Range Name to recalculate.

[tlafferty]

Stuck...sorry. Maybe attach your book and give this a bump and see if you get a response from another member.

[foxguy]

bump......

[TMS]

@foxguy: given your experience in the forum, you're not giving Tom much to go on ... or anyone else.

Unless it's a guessing game for which you are the host, there doesn't seem much point offering suggestions which aren't "relevant"

Regards

[snb]

Why so complicated ?

if basecol is fixed ("$CC$22") the result of the function will be fixed too: columns("CC")

So the named range 'Base' could simply be written as:

Please Login or Register  to view this content.

[foxguy]

TMShucks;
While replying I think I figured out what's causing the Range Name to recalculate all the time.

Range("Base") is on Sheets("R").
On Sheets("P") I have a column of formulas
'P'!BE20: "=INDEX('R'!Base,$BA20)", copied down the column.
'P'!BA20: "=MATCH($D20,'R'!Codes,0)", copied down the column.
'P'!D20: is a # (no formula).
'R'!Codes is a column of #s (no formulas).

But:
Range Name 'R'!Codes uses "Offset(....)" in it's RefersTo. Offset() is Volatile (which is why I'm trying to get rid of it), so 'R'!Codes gets recalculated every time the sheet is calculated and every formula and Name that refers to 'R'!Codes get recalculated right along with it. It seems obvious now.

But why does the sheet calculate when I move the cursor?

I think that what you are referring to is my tendency to keep my questions generic. Too many times I have given an example of what I'm trying to figure out and people reply with a solution that only works on the specific example and not on the bigger problem. So I try real hard to make it hard for someone to solve the specific example and force them to concentrate on the bigger problem. This time it looks like it backfired on me, because the solution seems so obvious now.

Thanks for forcing me to look at it from a different angle.

[foxguy]

snb;

Please Login or Register  to view this content.

Indirect is also volatile, which is what I'm trying to get rid of.
I can't hard code the column ("$C:$C") because that doesn't allow "CC18:CC200" to be cut and pasted somewhere else.

[snb]

I assume your post must be loaded with typos; your answer doesn't make any sense to me.

[TMS]

@foxguy:

Too many times I have given an example of what I'm trying to figure out and people reply with a solution that only works on the specific example and not on the bigger problem.

When I ask for a sample workbook, my ideal is a subset of the actual workbook which contains a subset of de-sensitized actual/typical data in a worksheet and workbook which reflects the structure of the real thing. In that case, any solution that I propose is likely to be scaleable and transferrable to the live workbook.

If the sample data and workbook does not reflect the live situation, the solution can only be "in principle" and, of necessity, require some adaption to fit the live workbook ... not always with any great ease.

In this particular case, the only way that I can think of to maintain a dynamic named range is using INDEX rather than INDIRECT or OFFSET.

Regards, TMS

[foxguy]

snb;
What I'm saying is that your formula doesn't accomplish what I'm looking for.

A formula that uses "Indirect(....)" is volatile, which is what I'm trying to get rid of. Currently I have hundreds of Names that use "Offset(....)" and they keep recalculating every time the spreadsheet calculates and slows down the workbook. So replacing "Offset" with "Indirect" just makes it worse (I have a different workbook that had thousands of formulas using "Indirect", because I didn't realize it was volatile. When someone pointed that out to me, I couldn't believe how much faster it was when I got rid of the "Indirect"s.

Also I can't use a formula that has "C:C" in it because sometimes the user of this file cuts a section of the column and pastes it somewhere else on the sheet. So if the formula includes "Index(C:C,.....)" doesn't work. If the user cuts C18:C6000 and pastes it into any other column (say F18:F6000) the formula still remains "Index(C:C,.....)" , but the data has been moved to column F. That's why I need a UDF(). If the formula is "Index(WC(C18),......)" and the user moves C18:C6000 to F20:F6002 the formula changes to "Index(WC(F20).....)".

I appreciate any help you give me, and I don't want to just ignore you when your suggestions don't work. It always bugs me when I offer a suggestion and the member doesn't respond to let me know why my suggestions don't work for him/her.

[foxguy]

Well, I was wrong.
I have created a Range Name that uses a UDF(). It is not dependent on anything else in the workbook.
Everytime I change cells the Name executes the UDF() 26 times.
I have no idea what is triggering the Name to recalculate.

Please Login or Register  to view this content.

My Worksheet_SelectionChange() puts the Selection.Row and Selection.Column into cells and there are thousands of cells that are dependent on those 2 cells, so that explains why the sheet recalculates when I change cells.

But why is Codes recalculating? How can find what is triggering Codes to recalculate?