PDA

View Full Version : Solved: Explicit Range References Keep Changing



Opv
03-24-2012, 10:38 AM
I'm having an issue with my explicit range references constantly changing. For example, if I utilize an array function and include a range, "$W1:$W10000" in variably during the course of editing my worksheet all such references get changed to "$W1:$W994". My problem is that as I add new rows of data the new rows aren't getting included in the calculations. How can I stop this from happening, or is there a way to create an automatically expanding range (without using VBA) so that newly created rows are included within the prescribed range?

Paul_Hossler
03-24-2012, 10:54 AM
If you insert rows or columns inside the reange, it expands to include them

If you add to the end, it does not

Depending on what you're doing, you could use a dynamic named range:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

Paul

Opv
03-24-2012, 11:07 AM
If you insert rows or columns inside the reange, it expands to include them

If you add to the end, it does not

Depending on what you're doing, you could use a dynamic named range:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

Paul

I see. I have been adding new rows onto the end rather than insert new rows before adding the new data, so that has been causing part of my problem. I think the suggested dynamic range will serve my purpose well. Thanks.

mikerickson
03-24-2012, 11:37 AM
The change from $W1:$W10000 to $W1:$W994 suggests that 6 rows have been removed, not added.

But the dynamic range approach should handle either additions or deletions.

Could I suggest another formulation of a dynamic named ranges that doesn't use the volatile OFFSET.

=INDEX(Sheet1!$W:$W, 1, 1):INDEX(Sheet1!$W:$W, COUNTA(Sheet1!$W:$W), 1)

Opv
03-24-2012, 11:59 AM
The change from $W1:$W10000 to $W1:$W994 suggests that 6 rows have been removed, not added.

But the dynamic range approach should handle either additions or deletions.

Could I suggest another formulation of a dynamic named ranges that doesn't use the volatile OFFSET.

=INDEX(Sheet1!$W:$W, 1, 1):INDEX(Sheet1!$W:$W, COUNTA(Sheet1!$W:$W), 1)

Very cool. Thanks for the help.

Paul_Hossler
03-24-2012, 12:19 PM
the volatile OFFSET.



http://dictionary.reference.com/browse/volatile?s=t

tending or threatening to break (http://dictionary.reference.com/browse/break) out into open violence; explosive: a volatile political situation.


"volatile" ??? :rotlaugh: We can't have that

Do you mean as in 'Application.Volatile'?

Is there a downside to OFFSET? I typically use Offset because it's easier for me to follow, but if the INDEX offers advantages, I'll change my engrained habits

Paul

mikerickson
03-24-2012, 12:33 PM
A formula that uses a volatile function, like OFFSET or TODAY or INDIRECT or a few others, recalculates every time any cell in the workbook is changed.

Non-Volatile functions, like INDEX, only recalculate if one of the precident cells of that formula is changed.

It has to be that way if OFFSET is to work properly.
Consider the formulas =SUM(B1:B10) and =SUM(OFFSET(A1,0,1,10,1))
They both do the same thing and return the same result.

The first formula recalculates only when one of the precedent cells (B1, B2, ..., B10) is changed.
This restriction of recalculation was introduced with the first version of Excel and is one reason that it became the gold standard for spreadsheets. The time savings are impressive.

The OFFSET formula has only one precedent cell (A1). In order to return the correct result, it has to be recalculated every time any cell in the sheet is changed. Otherwise the user could change B3 (a non-precedent cell) and the formula would show the old (inaccurate) result.

In small sheets the difference in performance isn't noticeable, but in a sheet where there are a lot of volatile functions or where volatile functions are used in array formulas, the slowing is very apparent.

Dynamic Named ranges are often used in array formulas. Constructing them with a non-volatile formulation will speed a spreadsheet dramatically.