E

#### exceluser

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