Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Kim Kim is offline
external usenet poster
 
Posts: 284
Default Vlookup Error Message - Excel 2003

I have two vlookup functions in one spreadsheet linking data from two other
spreadsheets. I have 23K rows and I received an error of "Excel cannot
complete this task with available resources. Choose less data or close other
applications." My IT department is saying that I should query data in a table
(access) rather than storing data of 20K + rows using the Vlookup in excel.
Shouldn't excel be able to handle 600K rows of data using the Vlookup
function?
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Vlookup Error Message - Excel 2003


Kim;462915 Wrote:
I have two vlookup functions in one spreadsheet linking data from two
other
spreadsheets. I have 23K rows and I received an error of "Excel cannot
complete this task with available resources. Choose less data or close
other
applications." My IT department is saying that I should query data in a
table
(access) rather than storing data of 20K + rows using the Vlookup in
excel.
Shouldn't excel be able to handle 600K rows of data using the Vlookup
function?


I doubt Excel 2003 would be able to handle 600k rows since there's a
limit per sheet of about 65k rows, however I imagine Excel would easily
be able to handle 20k+ rows for a VLookUp, the question is, can the
machine it's running on do so?


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=128019

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,939
Default Vlookup Error Message - Excel 2003

Check out this link on memory and calculations...

http://www.decisionmodels.com/index.htm

If you really mean 600k rows then you are defintly in trouble here. If you
meant 60k rows from an external spreadsheet then you are still in a bit of
hot water but you might be able to tweak things to make them work.

As opposed to a vlookup which has pile of overhead you could look at using
an external database query to the sheets involved (a much smaller footprint).

--
HTH...

Jim Thomlinson


"Kim" wrote:

I have two vlookup functions in one spreadsheet linking data from two other
spreadsheets. I have 23K rows and I received an error of "Excel cannot
complete this task with available resources. Choose less data or close other
applications." My IT department is saying that I should query data in a table
(access) rather than storing data of 20K + rows using the Vlookup in excel.
Shouldn't excel be able to handle 600K rows of data using the Vlookup
function?



  #6   Report Post  
Posted to microsoft.public.excel.misc
Kim Kim is offline
external usenet poster
 
Posts: 284
Default Vlookup Error Message - Excel 2003

That's exactly what I questioned and my IT Dept says that the machine Dell
Optiplex 755 with 2 GB of RAM, intel core 2 vpro processor, should be able to
handle the amount of data I am dealing with, but they said that excel Vlookup
should not be the tool I use to do my data comparison (I don't buy it).

I am trying to determine if the error I am receiving is my machine or the
Vlookup function.

"p45cal" wrote:


Kim;462915 Wrote:
I have two vlookup functions in one spreadsheet linking data from two
other
spreadsheets. I have 23K rows and I received an error of "Excel cannot
complete this task with available resources. Choose less data or close
other
applications." My IT department is saying that I should query data in a
table
(access) rather than storing data of 20K + rows using the Vlookup in
excel.
Shouldn't excel be able to handle 600K rows of data using the Vlookup
function?


I doubt Excel 2003 would be able to handle 600k rows since there's a
limit per sheet of about 65k rows, however I imagine Excel would easily
be able to handle 20k+ rows for a VLookUp, the question is, can the
machine it's running on do so?


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=128019


  #7   Report Post  
Posted to microsoft.public.excel.misc
Kim Kim is offline
external usenet poster
 
Posts: 284
Default Vlookup Error Message - Excel 2003

I used 600K rows as an example of what I thought excel could handle using the
Vlookup function. My current situation is only dealing with 23K rows of data.
I will check out this link you supplied, thanks.

"Jim Thomlinson" wrote:

Check out this link on memory and calculations...

http://www.decisionmodels.com/index.htm

If you really mean 600k rows then you are defintly in trouble here. If you
meant 60k rows from an external spreadsheet then you are still in a bit of
hot water but you might be able to tweak things to make them work.

As opposed to a vlookup which has pile of overhead you could look at using
an external database query to the sheets involved (a much smaller footprint).

--
HTH...

Jim Thomlinson


"Kim" wrote:

I have two vlookup functions in one spreadsheet linking data from two other
spreadsheets. I have 23K rows and I received an error of "Excel cannot
complete this task with available resources. Choose less data or close other
applications." My IT department is saying that I should query data in a table
(access) rather than storing data of 20K + rows using the Vlookup in excel.
Shouldn't excel be able to handle 600K rows of data using the Vlookup
function?

  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 120
Default Vlookup Error Message - Excel 2003

if you have 600K rows of data, your IT is correct to suggest using a database
application. However, for 20K rows ++ of data, Excel can handle that.

Try closing other applications that are running at the same time
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"Kim" wrote:

I have two vlookup functions in one spreadsheet linking data from two other
spreadsheets. I have 23K rows and I received an error of "Excel cannot
complete this task with available resources. Choose less data or close other
applications." My IT department is saying that I should query data in a table
(access) rather than storing data of 20K + rows using the Vlookup in excel.
Shouldn't excel be able to handle 600K rows of data using the Vlookup
function?

  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,939
Default Vlookup Error Message - Excel 2003

Check out the link I sent you. It is not a machine issue. XL has memory
limits that you will reach long before you reach your machines limit. Your
problem is that vlookup has huge overhead especially if you are looking at
external files. Relational databases handle this kind of thing MUCH better.
--
HTH...

Jim Thomlinson


"Kim" wrote:

That's exactly what I questioned and my IT Dept says that the machine Dell
Optiplex 755 with 2 GB of RAM, intel core 2 vpro processor, should be able to
handle the amount of data I am dealing with, but they said that excel Vlookup
should not be the tool I use to do my data comparison (I don't buy it).

I am trying to determine if the error I am receiving is my machine or the
Vlookup function.

"p45cal" wrote:


Kim;462915 Wrote:
I have two vlookup functions in one spreadsheet linking data from two
other
spreadsheets. I have 23K rows and I received an error of "Excel cannot
complete this task with available resources. Choose less data or close
other
applications." My IT department is saying that I should query data in a
table
(access) rather than storing data of 20K + rows using the Vlookup in
excel.
Shouldn't excel be able to handle 600K rows of data using the Vlookup
function?


I doubt Excel 2003 would be able to handle 600k rows since there's a
limit per sheet of about 65k rows, however I imagine Excel would easily
be able to handle 20k+ rows for a VLookUp, the question is, can the
machine it's running on do so?


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=128019


  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,939
Default Vlookup Error Message - Excel 2003

Closing other apps will not help. Check out memory at the following link...

http://www.decisionmodels.com/index.htm

--
HTH...

Jim Thomlinson


"Francis" wrote:

if you have 600K rows of data, your IT is correct to suggest using a database
application. However, for 20K rows ++ of data, Excel can handle that.

Try closing other applications that are running at the same time
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"Kim" wrote:

I have two vlookup functions in one spreadsheet linking data from two other
spreadsheets. I have 23K rows and I received an error of "Excel cannot
complete this task with available resources. Choose less data or close other
applications." My IT department is saying that I should query data in a table
(access) rather than storing data of 20K + rows using the Vlookup in excel.
Shouldn't excel be able to handle 600K rows of data using the Vlookup
function?

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Error message adaytum.xla when closing workbook in Excel 2003 Laura2008 Excel Discussion (Misc queries) 1 November 5th 08 07:36 PM
Excel 2003 Error Message Marilyn Excel Discussion (Misc queries) 0 December 11th 06 06:22 PM
error message during vlookup Deb Pingel Excel Discussion (Misc queries) 2 August 17th 06 04:39 PM
Document Not Saved Error Message - Excel 2003 glomonkey Excel Discussion (Misc queries) 1 January 25th 06 05:02 PM
VLOOKUP error message Janeen Excel Discussion (Misc queries) 7 December 27th 05 09:55 PM


All times are GMT +1. The time now is 11:54 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"