#1
|
|||
|
|||
excel cannot complete this task with available resources - vlookup
Hi,
I have a workbook with many rows - it will have up to 20000 of them. I use other small workbooks that contain vlookup formula connected to the mentioned big workbook. As long as both files are open I have no problems but having big workbook closed (it should stay closed as many employees will take fresh data from it) causes the following - if I select range up to A2;I8000 in a small workbook everything is fine, but exceeding 8000 for example A2;I8500 leads to "excel cannot complete this task with available resources" error. I seemed to have a solution to this problem but the whole formula acts strange. Creating a small range past 8000 value as for instance A8000:I8050 still causes same error despite having only 51 rows to process. As result it makes impossible to order a series of vlookup checks within smaller ranges with "if" formula depending on whether a given value is found (as I understand it would be a viable workaround if not the given problem). The mentioned vlookup formula is the only formula using range in both workbooks and is located in only one cell. Is there any solution to bypass this error? Last edited by Taisho; 01-24-2012 at 11:21 AM. |
#2
|
||||
|
||||
Hi Taisho,
Is your copy of Office 2003 fully updated? Have you tried repairing it (Help|Detect & Repair)?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
||||
|
||||
Another possibility is that your system has accumulated too many orphan files. Close Excel, then go to :
C:\Documents and Settings\*USERNAME*\Application Data\Microsoft\Excel (where *USERNAME* is your username) and delete all the files in that folder.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#4
|
|||
|
|||
Hello,
Thanks for an answer, unfortunately the problem is being experienced by other users who have office 2007/2010 installed as well. Also I tried deleting files from the mentioned path before and it didn't give any positive results. |
#5
|
||||
|
||||
Hi Taisho,
There may be some limit you're reaching/exceeding with your lookup. A possible workaround would be to have a 'helper' sheet in your smaller workbook that uses simple formulae to pull in the data from the large workbook, and have your lookup interrogate the data on the helper sheet. Of course, that'll make the smaller workbook much larger than it now is, but that might be the price you'll have to pay.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to set task to be done by 4 resources each one after another? | lukusm | Project | 1 | 01-16-2012 07:21 AM |
% Complete (Actual) vs % Complete (Should) | Carlos Maximo | Project | 3 | 12-06-2011 12:01 PM |
Excel Vlookup | jtej009 | Excel | 1 | 02-10-2011 03:25 AM |
Stop the % Complete of subatasks from counting against the Main task | bzeyger | Project | 0 | 06-08-2010 06:19 AM |
VLOOKUP in Excel with grade book. | Sailorcancer | Excel | 3 | 04-15-2010 09:55 PM |