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
|