Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel: External reference?

Posted on 2012-03-12
5
Medium Priority
?
285 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 33

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

722 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