Named Ranges Extremely Slw To Calculate
 

Good Evening,
I have converted most of my vlookup functions to use named ranges rather
than cell references, using offset & counta combined in the named ranges to
specify the range - as the rows will increase as transactions are added - so
it knows to go to the last row.
However, this has drastcally increased the claculation time - 5 mins!

Am I doing something wromg?
Thank you,
Mathew

Named Ranges Extremely Slw To Calculate
 

Have you checked your named ranges to see that they are as desired.
f5gototype in the name of the range to see what is highlighted.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Mathew P Bennett" wrote in message
...
Good Evening,
I have converted most of my vlookup functions to use named ranges rather
than cell references, using offset & counta combined in the named ranges
to specify the range - as the rows will increase as transactions are
added - so it knows to go to the last row.
However, this has drastcally increased the claculation time - 5 mins!

Am I doing something wromg?
Thank you,
Mathew

Named Ranges Extremely Slw To Calculate
 

And exactly the same functions with regular ranges instead of named ranges
does take considerable less time?

Then I would check whether your named ranges are correct.

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"Mathew P Bennett" wrote:

Good Evening,
I have converted most of my vlookup functions to use named ranges rather
than cell references, using offset & counta combined in the named ranges to
specify the range - as the rows will increase as transactions are added - so
it knows to go to the last row.
However, this has drastcally increased the claculation time - 5 mins!

Am I doing something wromg?
Thank you,
Mathew

Named Ranges Extremely Slw To Calculate
 

using offset & counta

OFFSET is a volatile function and recalculates on *every* calculation. Here
are 2 examples of (simple) dynamic ranges:

=OFFSET(A1,,,COUNTA(A:A))
=A1:INDEX(A:A,COUNTA(A:A))

They both do exactly the same thing but one is volatile and one is not.

--
Biff
Microsoft Excel MVP


"Mathew P Bennett" wrote in message
...
Good Evening,
I have converted most of my vlookup functions to use named ranges rather
than cell references, using offset & counta combined in the named ranges
to specify the range - as the rows will increase as transactions are
added - so it knows to go to the last row.
However, this has drastcally increased the claculation time - 5 mins!

Am I doing something wromg?
Thank you,
Mathew