change of cells address

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?
> >

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?
> > >

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

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
>

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.
"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?
>

=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