Thanks for taking the time to assist with this!
No problem.
your question, "Do you select 463 cells and array-enter it?" Not exactly sure what you mean by this. But I enter the formula in cell H4 and then drag it down to H463.
In that case, look at your formula. Why is the first argument E4:E466? I believe it should be just E4.
First formula:
=IF(ISERROR(VLOOKUP(E4:E466,INDIRECT.EXT("'\\Frg1\shared\DCShare\EcnReports\["&A4&"_RItemsPartsX.xls]"&A4&"_RItemsParts'!$A$2:$R$500"),3,FALSE)),"-",VLOOKUP(E4:E466,INDIRECT.EXT("'\\Frg1\shared\DCShare\EcnReports\["&A4&"_RItemsPartsX.xls]"&A4&"_RItemsParts'!$A$2:$R$500"),3,FALSE))
I thought since the spreadsheet I need to get information from was on a shared directory and the file is not open on my desktop when the functions run that I needed to use INDIRECT.EXT as part of the vLOOKUP.
Yes, you need the INDIRECT.EXT unless you can do what I suggest below.
What I'm hearing you say is at the minimum remove the ISERROR "pretty factor" from each cell. Therefore the dredded #N/A will appear if it returns without success. I understand that.
Yes, but you don't have to live with the dreaded #N/A. What I am saying is this: In some empty column, say H, enter the =VLOOKUP(INDIRECT.EXT(...)) formula. This will contain the #N/A results for non-matches. Now, in the column where you want the real result, enter =IF(ISNA(H4),"-",H4). So, the column with the real results will *not* have any #N/A in it.
Edit: Depending on how many of these A$ & "_RItemsPartsX.xls" files you have, I would not be surprised if the largest drag on performance comes from having to look up all these different files.
Also, is it possible for you to do something about the structure of the files you are searching? If you merge them into a single table, you could use SQL here too. You would get *all* the desired results in a single step!
If what I'm hearing you say for the MS Query is correct than I would not need use LOOKUP in formula 2 as it currently is written. I've never worked with MS Query so I'll have to spend some time following what you suggest.
The sheet 650 does contain duplicates. I use LOOKUP because I want to pull the first occurence of the MATCH. However there are flaws in this method and ideally that data would be cleaned to eliminates all duplicates prior to using LOOKUP.
Before addressing the 2nd formula, clean up the first formula. If you still don't see acceptable performance then tackle the 2nd. [That said, if I were in your shoes, I would clean up both. But, that's me.]
Please clean up the 650 sheet. Your search will be so much easier to implement. Before my first post I tested my suggestion. And, in any case, I am a strong advocate of letting SQL do as much work as possible when it comes to data stored in relational tables.
Sheet 650 in its purest untouched form of extracted data is about 8 mb in size. So reducing its columns to only the data I need to MATCH and then pull would also increase performance. The actual size of sheet 650 would include 9,000 rows. Preferably I would not reduce it to the minimum of 1,100 (referenced in the formula) that includes the three catagories of data to MATCH against.
Why is this file so big? Does it also have the same kind of complicated formulas in it? If not, don't worry about the size. At least not yet. 8MB is not a big database. I would focus on the file cleaning and the SQL part first.
That said, I'll try to get through setting up the sheet to use MS Query.
Any other suggestions?
What? Isn't the above enough to keep you busy for the next...oh, several weeks? {grin}
