Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
named ranges? | Excel Worksheet Functions | |||
Do Vlookup calculate quicker using named ranges?! | Excel Worksheet Functions | |||
Named ranges | Excel Discussion (Misc queries) | |||
Charts calculate extremely slow in Beta 2 | Excel Discussion (Misc queries) | |||
Like 123, allow named ranges, and print named ranges | Excel Discussion (Misc queries) |