Named Ranges Extremely Slw To Calculate

  • Thread starter Thread starter Mathew P Bennett
  • Start date Start date
M

Mathew P Bennett

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
 
Have you checked your named ranges to see that they are as desired.
f5>goto>type in the name of the range to see what is highlighted.
 
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.
 
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.
 
Back
Top