copy column data from one sheet to a different column on another sheet

ejpuck

New Member
Joined
Nov 8, 2011
Messages
15
Ok, this shouldn't be that hard. It's very easy on google docs, but I cant find the equivalent excel answer.

I have two sheets, each with its own sets of headings. I am continuously importing data into the cells bellow the headings on one sheet.

When imported, I want that data to automatically post into the correct corresponding columns in the other sheet.

The following is an answer I found this answer elsewhere, but of course it doesn't apply to excel. BTW, I'm looking for the "fancier" solution as I have no idea how many entries I'll eventually have.

"Finally, if you want to be "fancy" and have one formula that brings over an entire column of data, rather than just one cell and copying the formula down the column, you can do an "arrayformula".
Example:
If you want everything in Column A to be in Column D, then you can enter this in Cell D1:
=arrayformula(A1:A)

If Column A is on Sheet 1, and you want this in Column D of Sheet 2, then enter this in cell D1 on Sheet 2:
=arrayformula('Sheet 1'!A1:A)"
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Will the column header be the exact same in both?
 
Upvote 0
Yes, I'm pretty sure headers will stay the same; but they will almost definitely be in a different position. IE "First Name" was in column A on sheet one, will now be column D on sheet 2.
 
Upvote 0
Assuming your first sheet is called Sheet1, put this in cell A2 on Sheet2 =INDIRECT(ADDRESS(ROW(B2),MATCH(A$1,Sheet1!$1:$1,0),,,"Sheet1")) and drag across to all the columns, then down as many rows as your data on Sheet1 will extend.
 
Last edited:
Upvote 0
Assuming your first sheet is called Sheet1, put this in cell A2 on Sheet2 =INDIRECT(ADDRESS(ROW(B2),MATCH(A$1,Sheet1!$1:$1,0),,,"Sheet1")) and copy across to all the columns, then down as many rows as your data on Sheet1 will extend.


This is giving me a value not available error. Also, I know I could simply use a vlookup, copy and paste all the way down....I'm trying to not have to copy and paste. I want ALL of the First names in column A...as many as there are or ever will be entered, to automatically populate a different column on a different sheet.

Just doesn't seem like this should be so hard. How did google docs get more user friendly than excel anyway?
 
Upvote 0
Are the column headers entered exactly the same? Make sure there are no leading or trailing spaces.
Also, are the column headers on Sheet1 in the top row?
Also, I'm assuming your data starts in A2 (under the headers) and continues to the right?
And Excel is a lot harder to use because you can do anything with it. Go write a macro in Google docs and then you can complain about Excel :)
Excel is GOD.
 
Upvote 0
Worked for me.

Here's Sheet1:

Excel Workbook
ABCD
1AppleBananaCatDog
21112131
32122232
43132333
54142434
Sheet1


And here's Sheet2:


Excel Workbook
ABCD
1CatBananaDogApple
22111311
32212322
42313333
52414344
Sheet2
 
Upvote 0
Are the column headers entered exactly the same? Make sure there are no leading or trailing spaces.
Also, are the column headers on Sheet1 in the top row?
Also, I'm assuming your data starts in A2 (under the headers) and continues to the right?
And Excel is a lot harder to use because you can do anything with it. Go write a macro in Google docs and then you can complain about Excel :)
Excel is GOD.

Well just color me stupid....I'm sure its user error. We can't all be perfect...

Here's the deal....
On sheet 1, (which is not named sheet 1), I have about 11 fields of property information in columns B-L.

I want to take the name column, and the Mailing address column and start a second sheet where I add even more columns of relevant data.

I want to enter data into sheet 1, but view it in sheet 2. I haven't finished building sheet 2 yet, so I'm not exactly sure where the new columns will be.

Is there no simple way to move an entire column on one sheet to a different column on a different sheet?
 
Upvote 0
In the formula I gave you, replace Sheet1 with whatever your sheet is named. I also assumed you're putting the header on sheet2 in cell A1. This can be adjusted.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top