Extract the value of about 30 different cell references out of 420 closed workbooks

Look at attached file and tell me if it's setup correctly. (This is something you should do, BYW.)

How are the sheets named - employee name, employee number or what?

I did have to read the employee name a couple of times to make sure that I didn't see what I thought that I'd seen....

Edit: need to start over. Ignore this post.

Never mind... isn't the term Blow J** pretty much universal??

Seriously, though; I'm still looking at this. I am assuming that you want to pick up a person's name from a cell, look up that sheet and then return cell values. There are VERY limited options. As you're probably aware, INDIRECT, which would be just the job doesn't work on closed sheets. There are a few udfs out there, but have a tendency to be horribly slow. You have just so many data points that you want to return, this is likely to be a major problem. I've just frozen my PC looking at one of the options. Off to try another, before reporting back....

Best shot... but not optimistic. Requires .xlsm to work.

1. Unzip staff folder and put it, say, on desktop. DL destination, also, say to desktop.

2. Create a named range called fl CTRL-F3
=FILES("C:\Users\Dglenn\Desktop\Staff\*")&T(NOW())

Replace the details with YOUR full path.

3. With your master sheet in a DIFFERENT folder, this in B4, copied down:
=IFERROR(LEFT(INDEX(fl,ROW()-2),LEN(INDEX(fl,ROW()-2))-5),"")

This will generate a list of all of the files in the desktop folder called "Staff".

4. Code, pasted into a module:

Please Login or Register  to view this content.

How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)
[B][I]


Then in yellow shaded cells (I've only done a few) variants on this formula, which was copied from A3:

=IF($B3="","",IndirectEx("'C:\Users\Dglenn\Desktop\Staff\["&$B3&".xlsx]Sheet1'!B5"))

Again, you'll need to change the path to suit your PC. And change the B5 to reflect the cell content that you're trying to retrieve.

Now go away and have a cup of tea.... or a 3 course meal while it works through ....Try it with just a couple of fields first...