change of cells address

[Darius]

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?

 

[JLatham]

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]

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?

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

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?

 

[JLatham]

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.

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?

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

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]

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.)

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.

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?

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

:

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?

 

[JLatham]

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.

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.)

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.

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?

:

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

:

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?

 

[JLatham]

Since OFFSET failed so miserably (my apology for sending you down that dead
end), you may be just as easy off setting up the new formulas manually. You
could simply copy one that's set up properly and paste it where you need and
change the column and row number references.

But if your need for F17, G17, H17 and I17 are all on the same row, you
could enter the first formula as C:\me2\wa\sourcefile\[st_T_0.xls]!F$17
and copy across the row, the F would change to G, H and I automatically.
Same for the other workbooks.

I'll continue to think about it and maybe someone else will fall in here
with a better solution.

 

[Dave Peterson]

=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.

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.

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.)

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.

:

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?

:

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

:

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?

 

[JLatham]

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.

=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.

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.

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.

:

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?

:

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

:

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]

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.

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.

=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.

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.

:

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.

:

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?

:

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

:

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?

 

[Darius]

To be more clear, in a sheet (Sheet1 for example) of a workbook, I setup
reading data (lets say "data1" and put it in "A1") from a file (so many files
in fact). I read the number from a specific cell in the source file. Now in
this sheet (again sheet1) I go to column M for example and want to read some
other data from source file again which is in same column as "A1"but 6 rows
above the "data1" and put it in M14 for example. If I get first one right
then G14, H14 and I14 would be easy to o ahead.
I try your last suggestion.

Since OFFSET failed so miserably (my apology for sending you down that dead
end), you may be just as easy off setting up the new formulas manually. You
could simply copy one that's set up properly and paste it where you need and
change the column and row number references.

But if your need for F17, G17, H17 and I17 are all on the same row, you
could enter the first formula as C:\me2\wa\sourcefile\[st_T_0.xls]!F$17
and copy across the row, the F would change to G, H and I automatically.
Same for the other workbooks.

I'll continue to think about it and maybe someone else will fall in here
with a better solution.

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?

 

[Darius]

Since OFFSET failed so miserably (my apology for sending you down that dead
end), you may be just as easy off setting up the new formulas manually. You
could simply copy one that's set up properly and paste it where you need and
change the column and row number references.

But if your need for F17, G17, H17 and I17 are all on the same row, you
could enter the first formula as C:\me2\wa\sourcefile\[st_T_0.xls]!F$17
and copy across the row, the F would change to G, H and I automatically.
Same for the other workbooks.

I'll continue to think about it and maybe someone else will fall in here
with a better solution.

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?