An array covering multiple excel files

Hello all!

I am trying to cut down on a lot of manual input. Right now I enter in values everyday by finding specific work orders and copying a few lines of information from each one. Everday the work orders are different and each work order is its own excel file.

I would like, if possible, to use a vlookup whose array covered an entire folder of excel files, not just workbooks within one excel file. I would then only have to type in the work order number, and the equation would go searching for the file title with that number in it. All of our work orders are made from a premade template so everything I need are always in the same spots in every work order.

Any advice would be greatly appreciated.

Hrm, yea I would need the extension to be able to pull information from un-open files, otherwise I might as well spend the extra 5 sec copying the info.

As for the indirect, I'm not quite sure how that would work, can you give an example?

Here's kinda what I'd like:
Summary.xls
A | B
WO | PO
SAL1234 | AZ987765


So the PO is found from a specific cell in the file SAL1234.xls and everything in column A is going to be from a different .xls file. On top of that, I'm worried about how accurate the file name would have to be. For instance, the file SAL1234 is actually titled SAL1234 denver CO.xls but when inputting the lookup value, it would only be the first part.

No, its not all denver, CO. the general file name usually goes like this: (WO) (company) (state) (city).xls. And yes, another column can be used. However, remember that the only info given to me to look up the files is the WO. Looking up the company, state, and city to create an exact reference for the formula would be more work than what I'm doing now.

And sorry for my poor example, but column B was supposed to be what the formula returned. Column A is the reference cell and B is what is found in a specific cell ('[SAL1234 RandomCorp TX dallas.xls]WORK ORDER'!$J$6) within another excel file.

It would be easy enough to just say ='[SAL1234 RandomCorp TX dallas.xls]WORK ORDER'!$J$6. but I need an array for finding that specific file (SAL1234 RandomCorp TX dallas.xls) amongst all the other work orders based on the reference cell in Column A (SAL1234).

I hope that cleared it up...a bit :/

Wow, thats great. I've been playing around with the indirect.ext and I think it's a huge step. I also didn't know how to sub in a cell reference into a file path like that ("&A1&"). So, yes, now all that remains is to get the file name. Is there anyway to search directories or my computer? Or, in a vlookup type function you have the ability to choose the range (T or F) on how exact the match is...could I somehow implement that into finding an incomplete filename?

Eh, I can only do that if there's a formula to autogenerate a list of files in a folder.

Dang, well unless someone else has an idea, it looks like I'll have to do this in vba. And if I'm doing that already, I might as well do the whole thing and not just to find the filelist.

So it looks like I'll have to post this question in VBA. Thanks for help NBVC.