error: Not enough memory for VLOOKUP using external file

hi all,

we are encountering an error "Not enough memory" when we try to save
Pricing.xlt template file containing the following formula:
=VLOOKUP(B7,[_bpcs_pricing.xls]BPCS!$A1:$E$62050,2,FALSE)

we are using Microsoft Excel 97 SR-2

_bpcs_pricing.xls is an external file containing all our data
Pricing.xlt is a template file containing the following blank columns:
Item # auto increment starting from 1
Item code entered by the user
Item description retrieved by VLOOKUP from external file
List price retrieved by VLOOKUP from external file

Our goal is for the user to type an item code in Pricing.xlt, and VLOOKUP to
retrieve the respective item description and list price from
_bpcs_pricing.xls

Upon setting up Pricing.xlt and trying to save the file, the following error
message appears:
a) Not enough memory
b) Unable to save external link values

thanks in advance for any assistance

--
Trajano Roberto
P.S. we've originally posted this article under
microsoft.public.excel.worksheet.functions until we found
microsoft.public.excel.links.
we apologise for the cross post

Trajano Roberto wrote:
Upon setting up Pricing.xlt and trying to save the file, the following error
message appears:
a) Not enough memory
b) Unable to save external link values


Your other post contains a possible solution.

Does your pricing file really contain 62050 rows of valid information?
If so you should be thinking about using a database table for the pricing
file. If not you should consider using a range name for just the valid
information and using the range name in your formula, e.g.
=VLOOKUP(B7,[_bpcs_pricing.xls]BPCS!PriceData,2,FALSE)

In later versions of Excel you could set up a database query for each row that
would query the database when you entered an item code. In 97 you will need
to write a macro to do the same job. It would be a Worksheet_Change event
procedure in the module behind the sheet.


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup