Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel: External reference?

Posted on 2012-03-12
5
Medium Priority
?
292 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 1002 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 1002 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 34

Assisted Solution

by:Rob Henson
Rob Henson earned 498 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

885 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