Thanks Chris. Can anyone answer the question?

tmarc

New Member
Joined
Aug 27, 2002
Messages
8
Author This thread got dropped.
tmarc
Board Regular

Joined: Aug 28, 2002
Posts: 6 Posted: 2002-09-16 14:29
--------------------------------------------------------------------------------
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.




--------------------------------------------------------------------------------

Chris Davison
MrExcel MVP

Joined: Feb 16, 2002
Posts: 1384
From: Millbank, London, UK
Posted: 2002-09-17 01:03
--------------------------------------------------------------------------------
http://www.mrexcel.com/board/viewtopic.php?topic=21100&forum=2

it's still there.....
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
It's because you are using a (quasi) array formula - the source and target ranges must be the same.

In cell A3 of your Target enter:

=[source.xls]Sheet1!A2

Then copy down and across.

Incidentally, if you enter the formula by pointing at source.xls, pressing F4 cycles through the relative/absolute variations.
 

Forum statistics

Threads
1,182,136
Messages
5,933,852
Members
436,915
Latest member
Cygne volant

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top