Solved

Excel: External reference?

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

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
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…

746 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

13 Experts available now in Live!

Get 1:1 Help Now