I am trying to remove bottle necks and am curious
1) If I am better off using ranges in my lookups =LOOKUP(H17,S1:S100,T1:T100) vs =LOOKUP(H17,S:S,T:T)
2)Does clearing/deleting rows 100-65536 do the same as above
3) I am using one workbook with approx 30 worksheets (My file is 3MB). Am I better off having separate workbooks? I have 1 sheet as a master compiling all data from other 29 sheets and really don't need the sheets once the data is entered and compiled to master.
Why not have all the data on one sheet?
Defining the range for VLOOKUP, etc means that the formula is not checking the whole column
Unnecessary data/sheets should really be removed
Hope that helps.
RoyUK
--------
For Excel Tips & Solutions, free examples and tutorials why not check out my web site
Free DataBaseForm example
Used in this form
a) the LOOKUP will be equally efficient either way.
b) LOOKUP uses binary search in it's truest form meaning that even if the entire column was full of data it would still be blindingly fast to calculate.
By referencing a smaller range in the vectors you are however reducing the number of precedents that when altered would cause the LOOKUP to recalculate
(but again given the fact the calc is pretty much instantaneous that's no biggy)
edit:
In short, your bottlenecks are not the result of the above formulae - or at least I would be very surprised were that the case.
Last edited by DonkeyOte; 02-10-2011 at 10:01 AM.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks