How to use the text in a cell as the filename to find cell contents in

QuestionEdit

I am currently trying to write a master index file for a large number of spreadsheets used as the work dockets for a small factory. Each docket uses an identical layout, with the same information in the same cell on each.

Each file is named as the docket number, so I have been able to produce the entire file and path, formatted appropriately foras the contents of a cell. For example, the docket 10001 converts to the text: \\RYE\Users\john\My Documents\Index\[10001.xls]Sheet1 and by mapping the formula down I can easily produce that text for every spreadsheet (currently at over 12000, which is why I have no intention of linking by hand.)

The customer's name would then be : ='\\RYE\Users\John\My Documents\Index\[10001.xls]Sheet1'!$B$2

and the hours expected on the task would be =SUM('\\RYE\Users\John\My Documents\Index\[10001.xls]Sheet1'!$E$3:$E$77)

What I can't figure out, however, is how to replace the fixed filenames typed in the formulas with the text from the other cell. Ive even tried manually copying the created text into another cell with a paste special, in case the creating formula was the problem, as that wouldn't be too much of a task to update, but the adapted formula : ='D6'!$B$2 - where D6 is the cell storing the path and filename - just throws a #REF! error.

I don't even know if it's possible, but somehow I feel I should be able to make the adapted formula work - I'm hopefully overlooking a simplecommand I've never needed before.

AnswerEdit

What you want to do is doable using the INDIRECT function, but you're still going to have a problem because the file referenced by INDIRECT MUST be open. The only solution would be to use VBA to create the formula because you're still going to need the direct formula (like your SUM example) to be placed in a cell, because I assume you're not going to have the 12000 files open!)

This will take the text in B2 and make the formula in B6:

Sub MakeItAFormula()
   Range("B6").Formula = "=SUM('" & Range("B2").Value & "Sheet1'!$E$3:$E$77)"

End Sub

HTH