tmarc
Welcome to the Board
Joined: Aug 28, 2002
Posts: 5 Posted: 2002-09-04 10:09
--------------------------------------------------------------------------------
I would like to get information from one excel.xls to another excel.xls. I believe that the external formula/link will best serve my application. There is one hang-up. Linking a column, a row, or a rectangular region only links the upper right cell of the selected area.
The following example represents two “.xls” files, “source.xls” and “target.xls”
=[source.xls]Sheet1!$A$2:$C$5
On the target.xls, only the contains of A2 is linked
Source.xls
column A column B column C
a3.................b3..............c3
a4.................b4..............c4
a5.................b5..............c5
Target.xls
column A
???
--------------------------------------------------------------------------------
Chris Davison
MrExcel MVP
Joined: Feb 16, 2002
Posts: 1383
From: Millbank, London, UK
Posted: 2002-09-04 10:34
--------------------------------------------------------------------------------
Welcome to the Board,
you can adapt your method quite simply....
put your formula in your first cell. When it's entered, then highlight your entire destination range, hit F2 then hit control and enter, this should pop it into each cell in the destination range, with relative referencing applying despite the $$ anchoring
_________________
Hope this helps,
Chris
[Excel '97; Windows ME]
--------------------------------------------------------------------------------
tmarc
Welcome to the Board
Joined: Aug 28, 2002
Posts: 5 Posted: 2002-09-04 11:46
--------------------------------------------------------------------------------
Thanks, Chris, XL-Denis, and Swamp Thing.
--------------------------------------------------------------------------------
tmarc
Welcome to the Board
Joined: Aug 28, 2002
Posts: 5 Posted: 2002-09-04 12:18
--------------------------------------------------------------------------------
Ok It works, however the range in the target.xls must be in the same rows and columns as the source.xls. For instance if the range in the source is =[source.xls]Sheet1!$A$2:$C$5, and the range in the target is down one row =A3:C6, then press “F2, Ctrl + Enter” this is what is displayed in the target.xls.
a3 b3 c3
a4 b4 c4
a5 b5 c5
#VALUE! #VALUE! #VALUE!
What’s up with that?
Thanks again.
Welcome to the Board
Joined: Aug 28, 2002
Posts: 5 Posted: 2002-09-04 10:09
--------------------------------------------------------------------------------
I would like to get information from one excel.xls to another excel.xls. I believe that the external formula/link will best serve my application. There is one hang-up. Linking a column, a row, or a rectangular region only links the upper right cell of the selected area.
The following example represents two “.xls” files, “source.xls” and “target.xls”
=[source.xls]Sheet1!$A$2:$C$5
On the target.xls, only the contains of A2 is linked
Source.xls
column A column B column C
a3.................b3..............c3
a4.................b4..............c4
a5.................b5..............c5
Target.xls
column A
???
--------------------------------------------------------------------------------
Chris Davison
MrExcel MVP
Joined: Feb 16, 2002
Posts: 1383
From: Millbank, London, UK
Posted: 2002-09-04 10:34
--------------------------------------------------------------------------------
Welcome to the Board,
you can adapt your method quite simply....
put your formula in your first cell. When it's entered, then highlight your entire destination range, hit F2 then hit control and enter, this should pop it into each cell in the destination range, with relative referencing applying despite the $$ anchoring
_________________
Hope this helps,
Chris
[Excel '97; Windows ME]
--------------------------------------------------------------------------------
tmarc
Welcome to the Board
Joined: Aug 28, 2002
Posts: 5 Posted: 2002-09-04 11:46
--------------------------------------------------------------------------------
Thanks, Chris, XL-Denis, and Swamp Thing.
--------------------------------------------------------------------------------
tmarc
Welcome to the Board
Joined: Aug 28, 2002
Posts: 5 Posted: 2002-09-04 12:18
--------------------------------------------------------------------------------
Ok It works, however the range in the target.xls must be in the same rows and columns as the source.xls. For instance if the range in the source is =[source.xls]Sheet1!$A$2:$C$5, and the range in the target is down one row =A3:C6, then press “F2, Ctrl + Enter” this is what is displayed in the target.xls.
a3 b3 c3
a4 b4 c4
a5 b5 c5
#VALUE! #VALUE! #VALUE!
What’s up with that?
Thanks again.