Sometimes, I'll use a formula to build a string that looks like a formula. Then
convert to values and change the string to a real formula.

But I didn't understand enough to even do that.

JLatham wrote:

I thought about that also, and like you when I went back and read the
question again, I was at a loss as to even begin to figure out a response. I
also realized that the OP has at least 3 different workbooks he's pulling
data from. Looks like he may be trying to build some kind of summary book
from others, and INDEX() would probably work well for that type of thing.
But he doesn't say where the other formulas are, or anything else to give a
clue where to go for a solution.

"Dave Peterson" wrote:

=indirect() is another function (like =sumif(), =countif(), =offset()) that
won't work if the sending workbook is closed.

I was thinking of trying =index(), but I don't understand the question.



JLatham wrote:

Aaarggggghhhhh! You're no doubt correct, and I went to the trouble of
actually setting it up again to make sure it didn't go #VALUE! on me - didn't
recalculate. Now I've got to think up something else, thinking about some
INDIRECT, but I'll have to test that (better) also since I don't recall at
the moment if it works with the other workbook closed or not. All things
considered, probably less chance of that working than there was for a
straight reference to the cell.

"Dave Peterson" wrote:

Jerry,

I bet you haven't recalculated after you closed the sending workbook.

Select that cell with the formula and hit F2, then enter. I bet you get an
error. (I get #Value! in xl2003.)



JLatham wrote:

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?


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson