blakrapter
New Member
- Joined
- Oct 16, 2006
- Messages
- 13
Hello again,
I have another question for you guys. I am working with a spreadsheet that uses a lot of vlookup functions to pull data from various workbooks. The data is a price that is linked to the product ID. Some manufacturers give me the min price I can sell for, so basically, this is what I have in each price cell on the main price list (about 2,200 lines):
look for part number in workbook A, if available, then list price
if not, look for PN in workbook B, if available, then list price
if not, look for PN in workbook C, if available, then list price
if not, look for PN in workbook D, list price (has to be there)
The basic format I have is:
IF function looking at an ISNA function which calls a VLOOKUP if ISNA=0, else it goes to the next ISNA function until it finds one that =0. There are 4 of these functions stacked on top of each other this way, which gives a formula about 6 lines long and very inefficient since it updates about 2000 cells this way by pulling data from 3 lists with sizes ranging from 750 to 20,000 entries.
Here is the actual code:
When I update the price list (daily), it takes excel a good 1.5-2 minutes to update everything. Is there a way to do this more efficiently?
Thanks!
I have another question for you guys. I am working with a spreadsheet that uses a lot of vlookup functions to pull data from various workbooks. The data is a price that is linked to the product ID. Some manufacturers give me the min price I can sell for, so basically, this is what I have in each price cell on the main price list (about 2,200 lines):
look for part number in workbook A, if available, then list price
if not, look for PN in workbook B, if available, then list price
if not, look for PN in workbook C, if available, then list price
if not, look for PN in workbook D, list price (has to be there)
The basic format I have is:
IF function looking at an ISNA function which calls a VLOOKUP if ISNA=0, else it goes to the next ISNA function until it finds one that =0. There are 4 of these functions stacked on top of each other this way, which gives a formula about 6 lines long and very inefficient since it updates about 2000 cells this way by pulling data from 3 lists with sizes ranging from 750 to 20,000 entries.
Here is the actual code:
Code:
=IF(ISNA(IF(ISNA(VLOOKUP(A1262,'WorkbookA'!B$1:G$700,6,FALSE)=TRUE), IF(ISNA(VLOOKUP(A1262,'WorkbookB'!B$1:G$700,6,FALSE)=TRUE), VLOOKUP(A1262,WorkbookC'!A$1:X$15000,22,FALSE), VLOOKUP(A1262,'WorkbookB'!B$1:G$700,6,FALSE)), VLOOKUP(A1262,'WorkbookA'!B$1:G$700,6,FALSE)))=TRUE,VLOOKUP(A1262,'WorkbookD'!A1262:S$15000,7,FALSE),IF(ISNA(VLOOKUP(A1262,'WorkbookA'!B$1:G$700,6,FALSE)=TRUE), IF(ISNA(VLOOKUP(A1262,'WorkbookB'!B$1:G$700,6,FALSE)=TRUE), VLOOKUP(A1262,WorkbookC'!A$1:Y$15000,22,FALSE), VLOOKUP(A1262,'WorkbookB'!B$1:G$700,6,FALSE)), VLOOKUP(A1262,'WorkbookA'!B$1:G$700,6,FALSE)))
When I update the price list (daily), it takes excel a good 1.5-2 minutes to update everything. Is there a way to do this more efficiently?
Thanks!