Solved

Excel: External reference?

Posted on 2012-03-12
5
234 Views
Last Modified: 2012-03-20
Okay so I have two files. One is filled with data the other is empty. For certain reasons I want to copy/mirror columns and bring it over to the empty sheet in different column orders.

I'm doing this so I can simply reorganize columns and clean it up on the second empty file.  

So again I need to basically reference columns on file 1 and bring it over to the empty file 2.

I know there's a way to do it, something with external references, can anyone help me with a way to do this?

I have a example Screenshot showing an example of what I want attached:
example.PNG
0
Comment
Question by:Pancake_Effect
  • 2
  • 2
5 Comments
 
LVL 6

Accepted Solution

by:
reitzen earned 334 total points
ID: 37712584
The simplest way is to open both files and arrange them horizontally (View > Arrange All > Horizontal).

In the first row of the first column of the empty workbook, press the equal "=" sign to begin a formula and then click on the first row of the desired column in the data workbook.  Press ENTER to enter the formula.

Repeat for the remaining columns.  Then copy the entire first row down the number of rows that exist in the data workbook.

If you don't want to keep the external references "live" and only want the values in the new workbook, then copy the entire table and paste the values (Home > Paste > Paste Special > Values).
0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 37712607
For some reason it's not letting me arrange them horizontally. I get the option to doing what you said, but they don't combine. When I Googled this problem initially they all say, including you, to do this. But when I push arrange them horizontally that doesn't do or change anything. I have both files open right next to each other like in my screen shot.

I'm guessing it's necessary to arrange them, because every time I type in "=" then select the data in the other document, push enter, all it does it move down to the next cell..
0
 
LVL 6

Assisted Solution

by:reitzen
reitzen earned 334 total points
ID: 37713244
From your screen shot, it appears as though you have Excel open twice?  You can arrange the two sessions of Excel using Windows.  Right-click on the task bar (typically at the bottom of your screen) in a blank area.  Select "Tile windows horizontally".  This should give you the same effect.

The other way would be to open one of the files and then select File > Open from within Excel.  Once you navigate to the other file and click the Open button, you should be able to see both files (use CTRL + TAB).  Now you can organize the files horizontally.
0
 
LVL 32

Assisted Solution

by:Rob Henson
Rob Henson earned 166 total points
ID: 37719891
Alternatively, create what you want on two sheets in the same workbook. Then when you have that you can move the second sheet to another workbook. The external link will be created automatically.

Thanks
Rob H
0
 
LVL 4

Author Closing Comment

by:Pancake_Effect
ID: 37744439
Thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now