Searching and returning cell data from closed workbooks.

[awakening]

Hi

I have searched everywhere for this and apoligise if it is on the board already. I would be grateful for any help.

I need to create a workbook that has a cell for users to enter a seven digit ID number. When the number is entered, Excel will search through Row B of multiple Excel files and folders (all closed) for that number. If the number is found, the sheet will then return the name of the file, the name of its folder and the data that lies in the cell adjacent the number in Row C (which is a client's name).

Thanks

Chris

 

[Tazguy37]

Welcome to MrExcel Board!

Do you know what the sheet names will be? When I've done this from a closed workbook, I had to know the worksheet names ahead of time.

 

[awakening]

Thanks for the welcome.

The folders being searched will be called 'CR', 'Store' and 'Destroyed'. All three will be located in folder 'Archives'. The files in these folders will be called Box 1, Box 2, Box 3 etc...

Ideally the sheets used in these files would just be default - Sheet 1. Only one sheet is used in each. If they need to to be changed so they are unique, they can be the same name as their file (eg Box 1).

Thanks

Chris

 

[Tazguy37]

Here's one way. Very slick, Nimrod! There's other posts out there, for sure, if you search the board. HTH