Page 1 of 2 12 LastLast
Results 1 to 10 of 11

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

This is a discussion on copy column data from one sheet to a different column on another sheet within the Excel Questions forums, part of the Question Forums category; Ok, this shouldn't be that hard. It's very easy on google docs, but I cant find the equivalent excel answer. ...

  1. #1
    New Member ejpuck's Avatar
    Join Date
    Nov 2011
    Location
    Irving,TX
    Posts
    15

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

    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)"

  2. #2
    Board Regular
    The ONLY cool kid on the block
    BenMiller's Avatar
    Join Date
    Nov 2011
    Location
    Jerusalem
    Posts
    1,890

    Default Re: copy column data from one sheet to a different column on another sheet

    Will the column header be the exact same in both?
    "I'm not a perfectionist; I'm just perfect."

  3. #3
    New Member ejpuck's Avatar
    Join Date
    Nov 2011
    Location
    Irving,TX
    Posts
    15

    Default Re: copy column data from one sheet to a different column on another sheet

    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.

  4. #4
    Board Regular
    The ONLY cool kid on the block
    BenMiller's Avatar
    Join Date
    Nov 2011
    Location
    Jerusalem
    Posts
    1,890

    Default Re: copy column data from one sheet to a different column on another sheet

    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 by BenMiller; Feb 16th, 2012 at 06:50 PM.
    "I'm not a perfectionist; I'm just perfect."

  5. #5
    New Member ejpuck's Avatar
    Join Date
    Nov 2011
    Location
    Irving,TX
    Posts
    15

    Default Re: copy column data from one sheet to a different column on another sheet

    Quote Originally Posted by BenMiller View Post
    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?

  6. #6
    Board Regular
    The ONLY cool kid on the block
    BenMiller's Avatar
    Join Date
    Nov 2011
    Location
    Jerusalem
    Posts
    1,890

    Default Re: copy column data from one sheet to a different column on another sheet

    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.
    "I'm not a perfectionist; I'm just perfect."

  7. #7
    Board Regular
    The ONLY cool kid on the block
    BenMiller's Avatar
    Join Date
    Nov 2011
    Location
    Jerusalem
    Posts
    1,890

    Default Re: copy column data from one sheet to a different column on another sheet

    Worked for me.

    Here's Sheet1:

    Sheet1

     ABCD
    1AppleBananaCatDog
    21112131
    32122232
    43132333
    54142434


    Excel tables to the web >> Excel Jeanie HTML 4

    And here's Sheet2:


    Sheet2

     ABCD
    1CatBananaDogApple
    22111311
    32212322
    42313333
    52414344

    Spreadsheet Formulas
    CellFormula
    A2=INDIRECT(ADDRESS(ROW(B2),MATCH(A$1,Sheet1!$1:$1,0),,,"Sheet1"))
    B2=INDIRECT(ADDRESS(ROW(C2),MATCH(B$1,Sheet1!$1:$1,0),,,"Sheet1"))
    C2=INDIRECT(ADDRESS(ROW(D2),MATCH(C$1,Sheet1!$1:$1,0),,,"Sheet1"))
    D2=INDIRECT(ADDRESS(ROW(E2),MATCH(D$1,Sheet1!$1:$1,0),,,"Sheet1"))
    A3=INDIRECT(ADDRESS(ROW(B3),MATCH(A$1,Sheet1!$1:$1,0),,,"Sheet1"))
    B3=INDIRECT(ADDRESS(ROW(C3),MATCH(B$1,Sheet1!$1:$1,0),,,"Sheet1"))
    C3=INDIRECT(ADDRESS(ROW(D3),MATCH(C$1,Sheet1!$1:$1,0),,,"Sheet1"))
    D3=INDIRECT(ADDRESS(ROW(E3),MATCH(D$1,Sheet1!$1:$1,0),,,"Sheet1"))
    A4=INDIRECT(ADDRESS(ROW(B4),MATCH(A$1,Sheet1!$1:$1,0),,,"Sheet1"))
    B4=INDIRECT(ADDRESS(ROW(C4),MATCH(B$1,Sheet1!$1:$1,0),,,"Sheet1"))
    C4=INDIRECT(ADDRESS(ROW(D4),MATCH(C$1,Sheet1!$1:$1,0),,,"Sheet1"))
    D4=INDIRECT(ADDRESS(ROW(E4),MATCH(D$1,Sheet1!$1:$1,0),,,"Sheet1"))
    A5=INDIRECT(ADDRESS(ROW(B5),MATCH(A$1,Sheet1!$1:$1,0),,,"Sheet1"))
    B5=INDIRECT(ADDRESS(ROW(C5),MATCH(B$1,Sheet1!$1:$1,0),,,"Sheet1"))
    C5=INDIRECT(ADDRESS(ROW(D5),MATCH(C$1,Sheet1!$1:$1,0),,,"Sheet1"))
    D5=INDIRECT(ADDRESS(ROW(E5),MATCH(D$1,Sheet1!$1:$1,0),,,"Sheet1"))


    Excel tables to the web >> Excel Jeanie HTML 4
    "I'm not a perfectionist; I'm just perfect."

  8. #8
    New Member ejpuck's Avatar
    Join Date
    Nov 2011
    Location
    Irving,TX
    Posts
    15

    Default Re: copy column data from one sheet to a different column on another sheet

    Quote Originally Posted by BenMiller View Post
    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?

  9. #9
    Board Regular
    The ONLY cool kid on the block
    BenMiller's Avatar
    Join Date
    Nov 2011
    Location
    Jerusalem
    Posts
    1,890

    Default Re: copy column data from one sheet to a different column on another sheet

    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.
    "I'm not a perfectionist; I'm just perfect."

  10. #10
    New Member
    Join Date
    Feb 2010
    Posts
    13

    Default Re: copy column data from one sheet to a different column on another sheet

    Is there anyway to tweak the formula so 0s are not brought into for cells that have on value in Sheet1?

Page 1 of 2 12 LastLast

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com