VSB more efficient then standard functions?

[jan]

hi,

i have linked 1 worksheet to 2 other worksheets (all 3 in the same file)
using VLOOKUP

this in total pulls over 3 different cells into 3 different columns for all
65536 rows.
a update will take 30 minutes. i assume thsi could be more efficient,
however i don't know how to do that. any suggestions?

thanks.

 

[Gary''s Student]

65536 seems a little excessive. Can you limit the tables to less than this
value??

 

[Jim Thomlinson]

Trying to relate that that many cells is a problem for XL. One problem is you
blow through the 65,536 dependancy tree limit making all formulas act
volatile. The other is just the sheer number of calculations is overwhelming.
VBA or VBS is not going to help you in this case as they will operate slower
than vlookup. Your best option would probably be to use a relational database
which is designed for exactly this purpose.

Here is a link to calculation and general speed and memory performance...
http://www.decisionmodels.com/index.htm

 

[jan]

i might but still require a large number of rows. i use this to track/report
time entries from a large group of people over time.

 

[jan]

Jim,

is there a way to identify the last record in a worksheet, in that case i
see possibilities to limit it to those.

 

[Jim Thomlinson]

You can use a dynamic named range. Keep in mind that it uses the offest
function which is volatile...

http://www.cpearson.com/excel/excelF.htm#DynamicRanges

 

[Bob Phillips]

Rather than use 65536, use a method to determine the last used row, such as

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

and use that to determine the size of the data to be processed.