Solved

Excel: External reference?

Posted on 2012-03-12
5
244 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

803 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