Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup problem
I have two linked excel files, enquiry.xls and data.xls
The enquiry.xls is with the vlookup formula to lookup the data in data.xls. It works OK, however, when the data in data.xls upto row 5346, the lookup in enquriy.xls returns with the error message stating that the memory is not enough. There are 13 column (i.e. col A to M) in data.xls, and my PC is running Windows XP SP2, Office 2000 (Chinese) and with 1GB ram on board. What is the problem caused the error and how to correct it ? Thanks, |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup problem
Maybe you could open the data.xls before you open the enquiry.xls workbook. If
the "sending" workbook is open, I bet it'll be less intense for excel. And (just a silly guess), if you're bringing back the 13th column with the =vlookup() formula, maybe(???) using =index(match()) would be less stressful. And without seeing your formula, maybe you're using the whole column as the lookup table. Maybe limiting the range would be better, too. yclhk wrote: I have two linked excel files, enquiry.xls and data.xls The enquiry.xls is with the vlookup formula to lookup the data in data.xls. It works OK, however, when the data in data.xls upto row 5346, the lookup in enquriy.xls returns with the error message stating that the memory is not enough. There are 13 column (i.e. col A to M) in data.xls, and my PC is running Windows XP SP2, Office 2000 (Chinese) and with 1GB ram on board. What is the problem caused the error and how to correct it ? Thanks, -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup problem
Thanks Dave,
I tried to open the data.xls before enquiry.xls, it can't solve the problem. I was now working to modify the vlookup formulas in enquiry.xls, including limiting the column in the lookup table to the range of data. It still can't solve the problem. Since I'm not familar with using the =index(match()) formula, I'm now working to change the vlookup formula to =index(match()) formula. However, some more data needed to add to the data.xls regularly, is it with the limit to lookup the data rows in excel ? Or, some other way to solve the problem ? Thanks again in advance, "Dave Peterson" wrote: Maybe you could open the data.xls before you open the enquiry.xls workbook. If the "sending" workbook is open, I bet it'll be less intense for excel. And (just a silly guess), if you're bringing back the 13th column with the =vlookup() formula, maybe(???) using =index(match()) would be less stressful. And without seeing your formula, maybe you're using the whole column as the lookup table. Maybe limiting the range would be better, too. yclhk wrote: I have two linked excel files, enquiry.xls and data.xls The enquiry.xls is with the vlookup formula to lookup the data in data.xls. It works OK, however, when the data in data.xls upto row 5346, the lookup in enquriy.xls returns with the error message stating that the memory is not enough. There are 13 column (i.e. col A to M) in data.xls, and my PC is running Windows XP SP2, Office 2000 (Chinese) and with 1GB ram on board. What is the problem caused the error and how to correct it ? Thanks, -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup problem
I think the limit is dependent on what resources are available on your pc and
how much excel needs. Debra Dalgleish has lots of notes http://www.contextures.com/xlFunctions03.html (for =index(match())) I would have bet that having both workbooks open would resolve it. Sorry I was wrong. yclhk wrote: Thanks Dave, I tried to open the data.xls before enquiry.xls, it can't solve the problem. I was now working to modify the vlookup formulas in enquiry.xls, including limiting the column in the lookup table to the range of data. It still can't solve the problem. Since I'm not familar with using the =index(match()) formula, I'm now working to change the vlookup formula to =index(match()) formula. However, some more data needed to add to the data.xls regularly, is it with the limit to lookup the data rows in excel ? Or, some other way to solve the problem ? Thanks again in advance, "Dave Peterson" wrote: Maybe you could open the data.xls before you open the enquiry.xls workbook. If the "sending" workbook is open, I bet it'll be less intense for excel. And (just a silly guess), if you're bringing back the 13th column with the =vlookup() formula, maybe(???) using =index(match()) would be less stressful. And without seeing your formula, maybe you're using the whole column as the lookup table. Maybe limiting the range would be better, too. yclhk wrote: I have two linked excel files, enquiry.xls and data.xls The enquiry.xls is with the vlookup formula to lookup the data in data.xls. It works OK, however, when the data in data.xls upto row 5346, the lookup in enquriy.xls returns with the error message stating that the memory is not enough. There are 13 column (i.e. col A to M) in data.xls, and my PC is running Windows XP SP2, Office 2000 (Chinese) and with 1GB ram on board. What is the problem caused the error and how to correct it ? Thanks, -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup problem
Hi, Dave,
I have a idea to reduce the number of rows of the data.xls. In the data.xls, it contains the customers' information in columns : i.e. Tel No., Name, Flat No., Floor, Block/Tower, Address, District, ...... In enquiry.xls, when input the Tel. No., the related customer's info would list. However, some customers provided two tel. nos for regristation, in the current data.xls, I create two rows of duplicated records for each tel no. If the data.xls is modified to columns arrangement Tel. No1, Tel No2, Name, Flat No., Floor, ........, the data rows can be reduced a lot. But, how can I set the lookup formula in enquiry.xls to lookup the tel nos. in a cell and return the required customer's info. Thanks for your help, "Dave Peterson" wrote: I think the limit is dependent on what resources are available on your pc and how much excel needs. Debra Dalgleish has lots of notes http://www.contextures.com/xlFunctions03.html (for =index(match())) I would have bet that having both workbooks open would resolve it. Sorry I was wrong. yclhk wrote: Thanks Dave, I tried to open the data.xls before enquiry.xls, it can't solve the problem. I was now working to modify the vlookup formulas in enquiry.xls, including limiting the column in the lookup table to the range of data. It still can't solve the problem. Since I'm not familar with using the =index(match()) formula, I'm now working to change the vlookup formula to =index(match()) formula. However, some more data needed to add to the data.xls regularly, is it with the limit to lookup the data rows in excel ? Or, some other way to solve the problem ? Thanks again in advance, "Dave Peterson" wrote: Maybe you could open the data.xls before you open the enquiry.xls workbook. If the "sending" workbook is open, I bet it'll be less intense for excel. And (just a silly guess), if you're bringing back the 13th column with the =vlookup() formula, maybe(???) using =index(match()) would be less stressful. And without seeing your formula, maybe you're using the whole column as the lookup table. Maybe limiting the range would be better, too. yclhk wrote: I have two linked excel files, enquiry.xls and data.xls The enquiry.xls is with the vlookup formula to lookup the data in data.xls. It works OK, however, when the data in data.xls upto row 5346, the lookup in enquriy.xls returns with the error message stating that the memory is not enough. There are 13 column (i.e. col A to M) in data.xls, and my PC is running Windows XP SP2, Office 2000 (Chinese) and with 1GB ram on board. What is the problem caused the error and how to correct it ? Thanks, -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup problem
If you're matching on the first telephone number:
=vlookup(x1,'[otherworkbook.xls]Sheet1'!$A1:$E33,3,FALSE) Will return the 3rd column (name) from the table. yclhk wrote: Hi, Dave, I have a idea to reduce the number of rows of the data.xls. In the data.xls, it contains the customers' information in columns : i.e. Tel No., Name, Flat No., Floor, Block/Tower, Address, District, ...... In enquiry.xls, when input the Tel. No., the related customer's info would list. However, some customers provided two tel. nos for regristation, in the current data.xls, I create two rows of duplicated records for each tel no. If the data.xls is modified to columns arrangement Tel. No1, Tel No2, Name, Flat No., Floor, ........, the data rows can be reduced a lot. But, how can I set the lookup formula in enquiry.xls to lookup the tel nos. in a cell and return the required customer's info. Thanks for your help, "Dave Peterson" wrote: I think the limit is dependent on what resources are available on your pc and how much excel needs. Debra Dalgleish has lots of notes http://www.contextures.com/xlFunctions03.html (for =index(match())) I would have bet that having both workbooks open would resolve it. Sorry I was wrong. yclhk wrote: Thanks Dave, I tried to open the data.xls before enquiry.xls, it can't solve the problem. I was now working to modify the vlookup formulas in enquiry.xls, including limiting the column in the lookup table to the range of data. It still can't solve the problem. Since I'm not familar with using the =index(match()) formula, I'm now working to change the vlookup formula to =index(match()) formula. However, some more data needed to add to the data.xls regularly, is it with the limit to lookup the data rows in excel ? Or, some other way to solve the problem ? Thanks again in advance, "Dave Peterson" wrote: Maybe you could open the data.xls before you open the enquiry.xls workbook. If the "sending" workbook is open, I bet it'll be less intense for excel. And (just a silly guess), if you're bringing back the 13th column with the =vlookup() formula, maybe(???) using =index(match()) would be less stressful. And without seeing your formula, maybe you're using the whole column as the lookup table. Maybe limiting the range would be better, too. yclhk wrote: I have two linked excel files, enquiry.xls and data.xls The enquiry.xls is with the vlookup formula to lookup the data in data.xls. It works OK, however, when the data in data.xls upto row 5346, the lookup in enquriy.xls returns with the error message stating that the memory is not enough. There are 13 column (i.e. col A to M) in data.xls, and my PC is running Windows XP SP2, Office 2000 (Chinese) and with 1GB ram on board. What is the problem caused the error and how to correct it ? Thanks, -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup problem
Thanks, Dave,
How about also matching the 2nd tel no. in the same cell to lookup the related customer's info if the customer provides the 2nd tel no. for enquiry. "Dave Peterson" wrote: If you're matching on the first telephone number: =vlookup(x1,'[otherworkbook.xls]Sheet1'!$A1:$E33,3,FALSE) Will return the 3rd column (name) from the table. yclhk wrote: Hi, Dave, I have a idea to reduce the number of rows of the data.xls. In the data.xls, it contains the customers' information in columns : i.e. Tel No., Name, Flat No., Floor, Block/Tower, Address, District, ...... In enquiry.xls, when input the Tel. No., the related customer's info would list. However, some customers provided two tel. nos for regristation, in the current data.xls, I create two rows of duplicated records for each tel no. If the data.xls is modified to columns arrangement Tel. No1, Tel No2, Name, Flat No., Floor, ........, the data rows can be reduced a lot. But, how can I set the lookup formula in enquiry.xls to lookup the tel nos. in a cell and return the required customer's info. Thanks for your help, "Dave Peterson" wrote: I think the limit is dependent on what resources are available on your pc and how much excel needs. Debra Dalgleish has lots of notes http://www.contextures.com/xlFunctions03.html (for =index(match())) I would have bet that having both workbooks open would resolve it. Sorry I was wrong. yclhk wrote: Thanks Dave, I tried to open the data.xls before enquiry.xls, it can't solve the problem. I was now working to modify the vlookup formulas in enquiry.xls, including limiting the column in the lookup table to the range of data. It still can't solve the problem. Since I'm not familar with using the =index(match()) formula, I'm now working to change the vlookup formula to =index(match()) formula. However, some more data needed to add to the data.xls regularly, is it with the limit to lookup the data rows in excel ? Or, some other way to solve the problem ? Thanks again in advance, "Dave Peterson" wrote: Maybe you could open the data.xls before you open the enquiry.xls workbook. If the "sending" workbook is open, I bet it'll be less intense for excel. And (just a silly guess), if you're bringing back the 13th column with the =vlookup() formula, maybe(???) using =index(match()) would be less stressful. And without seeing your formula, maybe you're using the whole column as the lookup table. Maybe limiting the range would be better, too. yclhk wrote: I have two linked excel files, enquiry.xls and data.xls The enquiry.xls is with the vlookup formula to lookup the data in data.xls. It works OK, however, when the data in data.xls upto row 5346, the lookup in enquriy.xls returns with the error message stating that the memory is not enough. There are 13 column (i.e. col A to M) in data.xls, and my PC is running Windows XP SP2, Office 2000 (Chinese) and with 1GB ram on board. What is the problem caused the error and how to correct it ? Thanks, -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup problem
I would use multiple cells instead of using a long formula:
In one cell (say C2), look for a match using the first number: =vlookup(x1,'[otherworkbook.xls]Sheet1'!$A1:$E33,3,FALSE) In the adjacent cell (D2), look to see if it returned an error. If it returned an error, then look for a match in column B (notice the table changed). if(isna(C2),vlookup(x1,'[otherworkbook.xls]Sheet1'!$b1:$E33,2,FALSE),C2) (if the C2 didn't return an error, then it's brought over to D2.) Then hide column C. Then yclhk wrote: Thanks, Dave, How about also matching the 2nd tel no. in the same cell to lookup the related customer's info if the customer provides the 2nd tel no. for enquiry. "Dave Peterson" wrote: If you're matching on the first telephone number: =vlookup(x1,'[otherworkbook.xls]Sheet1'!$A1:$E33,3,FALSE) Will return the 3rd column (name) from the table. yclhk wrote: Hi, Dave, I have a idea to reduce the number of rows of the data.xls. In the data.xls, it contains the customers' information in columns : i.e. Tel No., Name, Flat No., Floor, Block/Tower, Address, District, ...... In enquiry.xls, when input the Tel. No., the related customer's info would list. However, some customers provided two tel. nos for regristation, in the current data.xls, I create two rows of duplicated records for each tel no. If the data.xls is modified to columns arrangement Tel. No1, Tel No2, Name, Flat No., Floor, ........, the data rows can be reduced a lot. But, how can I set the lookup formula in enquiry.xls to lookup the tel nos. in a cell and return the required customer's info. Thanks for your help, "Dave Peterson" wrote: I think the limit is dependent on what resources are available on your pc and how much excel needs. Debra Dalgleish has lots of notes http://www.contextures.com/xlFunctions03.html (for =index(match())) I would have bet that having both workbooks open would resolve it. Sorry I was wrong. yclhk wrote: Thanks Dave, I tried to open the data.xls before enquiry.xls, it can't solve the problem. I was now working to modify the vlookup formulas in enquiry.xls, including limiting the column in the lookup table to the range of data. It still can't solve the problem. Since I'm not familar with using the =index(match()) formula, I'm now working to change the vlookup formula to =index(match()) formula. However, some more data needed to add to the data.xls regularly, is it with the limit to lookup the data rows in excel ? Or, some other way to solve the problem ? Thanks again in advance, "Dave Peterson" wrote: Maybe you could open the data.xls before you open the enquiry.xls workbook. If the "sending" workbook is open, I bet it'll be less intense for excel. And (just a silly guess), if you're bringing back the 13th column with the =vlookup() formula, maybe(???) using =index(match()) would be less stressful. And without seeing your formula, maybe you're using the whole column as the lookup table. Maybe limiting the range would be better, too. yclhk wrote: I have two linked excel files, enquiry.xls and data.xls The enquiry.xls is with the vlookup formula to lookup the data in data.xls. It works OK, however, when the data in data.xls upto row 5346, the lookup in enquriy.xls returns with the error message stating that the memory is not enough. There are 13 column (i.e. col A to M) in data.xls, and my PC is running Windows XP SP2, Office 2000 (Chinese) and with 1GB ram on board. What is the problem caused the error and how to correct it ? Thanks, -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup problem
Thanks Dave, it works after numerous modification of the formulas in
enquiry.xls. Thanks a lot again, yclhk from Hong Kong "Dave Peterson" wrote: I would use multiple cells instead of using a long formula: In one cell (say C2), look for a match using the first number: =vlookup(x1,'[otherworkbook.xls]Sheet1'!$A1:$E33,3,FALSE) In the adjacent cell (D2), look to see if it returned an error. If it returned an error, then look for a match in column B (notice the table changed). if(isna(C2),vlookup(x1,'[otherworkbook.xls]Sheet1'!$b1:$E33,2,FALSE),C2) (if the C2 didn't return an error, then it's brought over to D2.) Then hide column C. Then yclhk wrote: Thanks, Dave, How about also matching the 2nd tel no. in the same cell to lookup the related customer's info if the customer provides the 2nd tel no. for enquiry. "Dave Peterson" wrote: If you're matching on the first telephone number: =vlookup(x1,'[otherworkbook.xls]Sheet1'!$A1:$E33,3,FALSE) Will return the 3rd column (name) from the table. yclhk wrote: Hi, Dave, I have a idea to reduce the number of rows of the data.xls. In the data.xls, it contains the customers' information in columns : i.e. Tel No., Name, Flat No., Floor, Block/Tower, Address, District, ...... In enquiry.xls, when input the Tel. No., the related customer's info would list. However, some customers provided two tel. nos for regristation, in the current data.xls, I create two rows of duplicated records for each tel no. If the data.xls is modified to columns arrangement Tel. No1, Tel No2, Name, Flat No., Floor, ........, the data rows can be reduced a lot. But, how can I set the lookup formula in enquiry.xls to lookup the tel nos. in a cell and return the required customer's info. Thanks for your help, "Dave Peterson" wrote: I think the limit is dependent on what resources are available on your pc and how much excel needs. Debra Dalgleish has lots of notes http://www.contextures.com/xlFunctions03.html (for =index(match())) I would have bet that having both workbooks open would resolve it. Sorry I was wrong. yclhk wrote: Thanks Dave, I tried to open the data.xls before enquiry.xls, it can't solve the problem. I was now working to modify the vlookup formulas in enquiry.xls, including limiting the column in the lookup table to the range of data. It still can't solve the problem. Since I'm not familar with using the =index(match()) formula, I'm now working to change the vlookup formula to =index(match()) formula. However, some more data needed to add to the data.xls regularly, is it with the limit to lookup the data rows in excel ? Or, some other way to solve the problem ? Thanks again in advance, "Dave Peterson" wrote: Maybe you could open the data.xls before you open the enquiry.xls workbook. If the "sending" workbook is open, I bet it'll be less intense for excel. And (just a silly guess), if you're bringing back the 13th column with the =vlookup() formula, maybe(???) using =index(match()) would be less stressful. And without seeing your formula, maybe you're using the whole column as the lookup table. Maybe limiting the range would be better, too. yclhk wrote: I have two linked excel files, enquiry.xls and data.xls The enquiry.xls is with the vlookup formula to lookup the data in data.xls. It works OK, however, when the data in data.xls upto row 5346, the lookup in enquriy.xls returns with the error message stating that the memory is not enough. There are 13 column (i.e. col A to M) in data.xls, and my PC is running Windows XP SP2, Office 2000 (Chinese) and with 1GB ram on board. What is the problem caused the error and how to correct it ? Thanks, -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup problem | Excel Discussion (Misc queries) | |||
VLOOKUP PROBLEM | Excel Discussion (Misc queries) | |||
Vlookup problem | Excel Worksheet Functions | |||
Vlookup problem | Excel Worksheet Functions | |||
Vlookup Problem | Excel Worksheet Functions |