Can you copy your formula and paste it into a message here?
=OFFSET('C:\Users\JLatham\Documents\[SourceBook.xls]T30'!$F$23,-12,0)
works fine for me even with the book closed. That one gives me the contents
of F11 in the other book.

Try building your formula up this way:
Open both workbooks. Go to the workbook with the formulas in it. Choose a
cell
start the formula by typing
=OFFSET(
then select the other workbook, sheet and cell and Excel will fill in all of
the information for you, it would look something like
=OFFSET('C:\Users\JLatham\Documents\[SourceBook.xls]T30'!$F$23
and you pick up by typing
,-6,0) and pressing the[Enter] key which will give you
=OFFSET('C:\Users\JLatham\Documents\[SourceBook.xls]T30'!$F$23,-6,0)
and should show you the value of T30!F17 in the other book. Now save/close
the other workbook, the formula should still show the proper value - Excel
will fill in the path to the file for you when you close it.

"Darius" wrote:

I need to write the path and workbook name and when I write it while the file
is closed then gives an error #value and when the file get open then the
error replace with right naswer but again if I close the files and open the
required file and not the source file the error is reamined there. Any
suggestion?

"JLatham" wrote:

You could look at the OFFSET() function.

In this example, I've skipped the path and workbook name, and just used the
worksheet and cell address, but you should get the idea:
=OFFSET(T30!$F$23,-6,0)
would be the same as
=T30!$F$17

"Darius" wrote:

I have a data sheet value as:
C:\me2\wa\sourcefile\[st_T_0.xls]T30!$F$23
C:\me2\wa\sourcefile\[st_T_10.xls]T30!$F$33
C:\me2\wa\sourcefile\[st_T_20.xls]T30!$F$43

now I want to get data in same sheet (T30) from F17, G17,H17,I17 in other
words each time "F" cell number minues 6 makes the first required cell
address e.g. F23 - 6 = F17 and then G17 etc.

How do I do that in an easy way?