Creating a high performance dynamic name

[exceluser]

Is there a way to improve the performance of the dynamic name below ?

The dynamic name uses the formula:

=OFFSET(Sheet1!$A$1,MATCH("Orange",Sheet1!$A:$A,0),,COUNTA(Sheet1!$A:
$A)-MATCH("Orange",Sheet1!$A:$A,0),)

On the worksheet named Sheet1:

A
1 Apple
2 Orange
3 Banana
4 Pear


The result will be $A$3:$A$4 and will automatically expand downward
when data is entered in A5, A6, etc.

The only optimizations I've come up with are:

1) Storing =MATCH("Orange",Sheet1!$A:$A,0) in B1 and then referring
to B1 in dynamic name’s formula

2) Storing =COUNTA(Sheet1!$A:$A) in B2 and then referring to B2 in
the dynamic name's formula

The dynamic name's formula would then be:

=OFFSET(Sheet1!$A$1,$B$1,,$B$2-$B$1,)

However, when this dynamic name is referenced 20,000+ times, this
change does little to reduce the calculation time.

This is especially time consuming when modification of an unrelated
cell causes a recalculation of all cells that reference this dynamic
name.

Is there a way to define this dynamic name without creating a volatile
formula ?




Exceluser

 

[Don Guillett Excel MVP]

Is there a way to improve the performance of the dynamic name below ?

The dynamic name uses the formula:

=OFFSET(Sheet1!$A$1,MATCH("Orange",Sheet1!$A:$A,0),,COUNTA(Sheet1!$A:
$A)-MATCH("Orange",Sheet1!$A:$A,0),)

On the worksheet named Sheet1:

        A
1       Apple
2       Orange
3       Banana
4       Pear

The result will be $A$3:$A$4 and will automatically expand downward
when data is entered in A5, A6, etc.

The only optimizations I've come up with are:

        1) Storing =MATCH("Orange",Sheet1!$A:$A,0) in B1 and then referring
to B1 in dynamic name’s formula

        2) Storing =COUNTA(Sheet1!$A:$A) in B2 and then referring to B2 in
the dynamic name's formula

The dynamic name's formula would then be:

        =OFFSET(Sheet1!$A$1,$B$1,,$B$2-$B$1,)

However, when this dynamic name is referenced 20,000+ times, this
change does little to reduce the calculation time.

This is especially time consuming when modification of an unrelated
cell causes a recalculation of all cells that reference this dynamic
name.

Is there a way to define this dynamic name without creating a volatile
formula ?

Exceluser

1. To make the item variable based on b1 simply change the "orange" to
$b$1. Be sure to use absolutes $. I don't understand part 2??

 

[exceluser]

Don,

Thanks for responding.

BACKGROUND

The dynamic name is being used to create a named range reference
where the start and last addresses in column A can fluctuate.

Entries in column A act as the data source. Over time, entries in
column A will be added and removed.

B1 acts as a helper calculation cell to determine the start address
of the dynamic range. $B$1 is used in the formula so the MATCH
function isn't invoked every time the dynamic name is referenced.

B2 acts as a helper calculation cell using the COUNTA function to
detect new entries in column A. If a new entry is input in A5, say
"Strawberries", the range created by the dynamic name will expand to
include the new entry. In this case, $A$3:$A$5. $B$2 is referenced in
the formula so the COUNTA function isn't invoked every time the
dynamic name is referenced.

Because the OFFSET function is volatile, using $B$1 and $B$2 in the
dynamic name's formula appears to be the only way to reduce the
calculation overhead.

UPDATE

To eliminate the volatility of the dynamic name's formula, the
formula is now based off the INDEX function instead of the OFFSET
function.

=INDEX(Sheet1!$A:$A,Sheet1!$B$1):INDEX(Sheet1!$A:$A,Sheet1!$B$2)

Now whenever a cell that's unrelated to the dynamic name is
modified, a recalculation of every formula that uses the dynamic name
is avoided.

This change alone makes working with the spreadsheet a lot more
tolerable.


Exceluser