Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-24-2012, 06:54 AM
Taisho Taisho is offline excel cannot complete this task with available resources - vlookup Windows XP excel cannot complete this task with available resources - vlookup Office 2003
Novice
excel cannot complete this task with available resources - vlookup
 
Join Date: Jan 2012
Posts: 2
Taisho is on a distinguished road
Default 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.
Reply With Quote
  #2  
Old 01-26-2012, 10:21 PM
macropod's Avatar
macropod macropod is offline excel cannot complete this task with available resources - vlookup Windows 7 64bit excel cannot complete this task with available resources - vlookup Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,617
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #3  
Old 01-26-2012, 10:27 PM
macropod's Avatar
macropod macropod is offline excel cannot complete this task with available resources - vlookup Windows 7 64bit excel cannot complete this task with available resources - vlookup Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,617
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #4  
Old 01-27-2012, 02:22 AM
Taisho Taisho is offline excel cannot complete this task with available resources - vlookup Windows XP excel cannot complete this task with available resources - vlookup Office 2003
Novice
excel cannot complete this task with available resources - vlookup
 
Join Date: Jan 2012
Posts: 2
Taisho is on a distinguished road
Default

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.
Reply With Quote
  #5  
Old 01-27-2012, 03:09 AM
macropod's Avatar
macropod macropod is offline excel cannot complete this task with available resources - vlookup Windows 7 64bit excel cannot complete this task with available resources - vlookup Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,617
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
excel cannot complete this task with available resources - vlookup How to set task to be done by 4 resources each one after another? lukusm Project 1 01-16-2012 07:21 AM
excel cannot complete this task with available resources - vlookup % Complete (Actual) vs % Complete (Should) Carlos Maximo Project 3 12-06-2011 12:01 PM
excel cannot complete this task with available resources - vlookup 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
excel cannot complete this task with available resources - vlookup VLOOKUP in Excel with grade book. Sailorcancer Excel 3 04-15-2010 09:55 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 08:03 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2022, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2022 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft