Using Cell Value in File Path

[sdcamp3020]

I have a summary log where I keep up with shop time on all active jobs. Column A contains the Job Folder Name and Cell B contains the Shop Hours excluding Field Hours for that particular job. The formula used to calculate these hours is:

='\\Server08\f\Sandra\Job Cost Logs\2011\[11-09670.xls]Job Summary'!$D$30-'\\Server08\f\Sandra\Job Cost Logs\2011\[11-09670.xls]Job Summary'!$D$28

Is there a way to substitute the value of the corresponding cell A for the file name? Such as using the value found in A151 in place of 11-09670.

Thanks!

 

[LCTrucido]

From the link below:

http://office.microsoft.com/en-us/e...nt-excel-file-name-in-a-cell-HA001034627.aspx
Insert the current file name only (for example, test.xls)

Enter the following formula to insert the name of the current file in a cell:
<code>=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)</code>
Note If you use this formula on an unsaved worksheet, you receive the error #VALUE!. When you save the worksheet, the error is replaced by the filename.

 

[sdcamp3020]

I am not trying to insert the current file name. I am using a formula to look up values in multiple workbooks out on our server all named for a particular job. The summary sheet I have pulls the shop time listed on the job summary page of the job workbook named in column A. Therefore I want my to replace the section "11-09670" in my formula with whatever is listed in Column A of the corresponding row. That way, when I add the next days open jobs ... 11-09680, 11-09690, 11-09700 ... I won't have to change the formula each time.

Thanks though!

 

[LCTrucido]

Sorry, read it wrong...

Not sure in that case.