Using worksheet name in reference to another workbook (1 Viewer)

[PeterWieland]

I have 2 seperate workbooks, both with many worksheets. I need to get some data from one of the workbooks and copy it to the other, and I need exactly the same cell range from each of the many worksheets (the worksheets have the same name in each workbook).

I have the following formula which works for one worksheet:-

='D:\Dissertation\Write-up\Spreadsheets\[2007buf_full_data.xlsm]4412A'!$K$31:$AB$34

What I want to do is automate this by having the worksheet name (4412A in the above example) derived from the current worksheet. This way I can copy the formula to all of the worksheets rather than having to write a new formula for each one.

I have found the command to get the current worksheet name and put it in a cell:-

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)

but I cannot work out how to put it in the first formula to replace the 4412A

Thanks in advance
Pete