Named Ranges Extremely Slw To Calculate

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
 
D

Don Guillett

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.
 
W

Wigi

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.
 
T

T. Valko

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Named Ranges & Calculation Times Query 5
Named ranges dissapear from list 4
Multiple Dynamic Named Ranges across Columns 3
How do I set up a Dynamic Named Ranges in Excel 2007 7
Redundant Named Ranges Linking to Other Files 1
Import changes named ranges 3
Copying Named Ranges in Chart Series 1
creating set of named ranges 2

Top