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

Help with excel formula (copy data from one sheet to another when value matches)

This is a discussion on Help with excel formula (copy data from one sheet to another when value matches) within the Excel Questions forums, part of the Question Forums category; Hi all, This is likely to be a pretty simple formula for most of you - your assistance would be ...

  1. #1
    New Member
    Join Date
    Aug 2008
    Posts
    12

    Default Help with excel formula (copy data from one sheet to another when value matches)

    Hi all,

    This is likely to be a pretty simple formula for most of you - your assistance would be very much appreciated!!

    I have two sheets (sheet 1 and sheet 2). Sheet 2 has a range of data about employees. Column A contains a unique reference number with the rest of the row (Column B - Column X) containing corresponding data about that empoyee.

    When I enter that unique reference number in Sheet 1, Column A, and matches the value in Sheet 2, Column A; I would like the rest of the corresponding row data (Column B - Column X) from Sheet 2 to populate 'automatically' (copied) in Sheet 1.

    I hope my explaination is clear??!

    Thanks in advance...

  2. #2
    New Member
    Join Date
    Aug 2008
    Posts
    12

    Default Re: Help with excel formula (copy data from one sheet to another when value matches)

    To assist, below is a (simplified) copy of Sheet 2

    EMPLOYEE REFERENCEADDRESSTELEPHONEDOBCLASSIFICATIONSALARY
    1234512 Black St1234561-Jan-721.1$60,000
    2345610 Black St2345671-Jan-731.1$60,000
    345678 Black St3456781-Jan-741.2$65,000
    456786 Black St4567891-Jan-752.1$70,000
    567894 Black St5678901-Jan-762.2$75,000
    678902 Black St6789011-Jan-773.1$80,000


    So my aim is when the value in Sheet 1, Column A matches Sheet 2, Column A, the additional rows which correspond are populated (in Sheet 1).

    There are many other values in both sheets, however these are the only ones which I would like to 'automatically' populate.

    Thanks

  3. #3
    Board Regular shemayisroel's Avatar
    Join Date
    Sep 2008
    Location
    Sydney - Australia
    Posts
    1,859

    Default Re: Help with excel formula (copy data from one sheet to another when value matches)

    Based on your sample data, something like this? I think I understood what you wanted?

    Sheet1

    ABCDEF
    1EMPLOYEE REFERENCEADDRESSTELEPHONEDOBCLASSIFICATIONSALARY
    2456786 Black St4567891-Jan-752.1$70,000

    Spreadsheet Formulas
    CellFormula
    B2=VLOOKUP($A$2,Sheet2!$A$2:$F$7,COLUMNS($A$1:B1),FALSE)
    C2=VLOOKUP($A$2,Sheet2!$A$2:$F$7,COLUMNS($A$1:C1),FALSE)
    D2=VLOOKUP($A$2,Sheet2!$A$2:$F$7,COLUMNS($A$1:D1),FALSE)
    E2=VLOOKUP($A$2,Sheet2!$A$2:$F$7,COLUMNS($A$1:E1),FALSE)
    F2=VLOOKUP($A$2,Sheet2!$A$2:$F$7,COLUMNS($A$1:F1),FALSE)


    Sheet2

    ABCDEF
    1EMPLOYEE REFERENCEADDRESSTELEPHONEDOBCLASSIFICATIONSALARY
    21234512 Black St1234561-Jan-721.1$60,000
    32345610 Black St2345671-Jan-731.1$60,000
    4345678 Black St3456781-Jan-741.2$65,000
    5456786 Black St4567891-Jan-752.1$70,000
    6567894 Black St5678901-Jan-762.2$75,000
    7678902 Black St6789011-Jan-773.1$80,000
    enercheenhologoskaihoogosenprostontheonkaitheosenhologosnarchhnoogovkailogohnprvtonqeonkaiqeovhnologov

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    63,619

    Default Re: Help with excel formula (copy data from one sheet to another when value matches)

    Sheet1

    A1:F7 houses the data, including the headers.

    Sheet2

    A1 houses an employee ID of interest, perhaps picked out from a data-validation list, instead of just typing...

    A2:

    =MATCH(A1,Sheet1!$A$2:$A$7,0)

    A4, copy across and down:

    =INDEX(B$2:B$7,$A2)

  5. #5
    New Member
    Join Date
    Aug 2008
    Posts
    12

    Default Re: Help with excel formula (copy data from one sheet to another when value matches)

    Shemayisroel - your solution does seem to work, but I am having some difficulties applying it.

    To clarify - the employee ID number (Sheet 1, Column A, as many rows as needed) is the only entry I am wanting to make.

    I have a sanitised version of the workbook if you are willing to look at it by PM; likewise Aladin Akyurek.

    Thanks for your help in the mean time...

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    63,619

    Default Re: Help with excel formula (copy data from one sheet to another when value matches)

    Quote Originally Posted by BravoBravo View Post
    Shemayisroel - your solution does seem to work, but I am having some difficulties applying it.

    To clarify - the employee ID number (Sheet 1, Column A, as many rows as needed) is the only entry I am wanting to make.

    I have a sanitised version of the workbook if you are willing to look at it by PM; likewise Aladin Akyurek.

    Thanks for your help in the mean time...
    Recap...

    Sheet2

    A1:F7...

    EMPLOYEE REFERENCEADDRESSTELEPHONEDOBCLASSIFICATIONSALARY
    1234512 Black St1234561-Jan-721.160000
    2345610 Black St2345671-Jan-731.160000
    345678 Black St3456781-Jan-741.265000
    456786 Black St4567891-Jan-752.170000
    567894 Black St5678901-Jan-762.275000
    678902 Black St6789011-Jan-773.180000


    Sheet1

    Emp Ref
    45678
    4
    ADDRESSTELEPHONEDOBCLASSIFICATIONSALARY
    6 Black St4567891-Jan-752.170000


    A2: 45678

    A3:

    =MATCH(A2,Sheet2!$A$2:$A$7,0)

    A5, copy across:

    =INDEX(Sheet2!B$2:B$7,$A$3)

  7. #7
    Board Regular shemayisroel's Avatar
    Join Date
    Sep 2008
    Location
    Sydney - Australia
    Posts
    1,859

    Default Re: Help with excel formula (copy data from one sheet to another when value matches)

    Quote Originally Posted by BravoBravo View Post
    Shemayisroel - your solution does seem to work, but I am having some difficulties applying it.

    To clarify - the employee ID number (Sheet 1, Column A, as many rows as needed) is the only entry I am wanting to make.

    I have a sanitised version of the workbook if you are willing to look at it by PM; likewise Aladin Akyurek.

    Thanks for your help in the mean time...
    Have you tried Aladins solution?

    if so what errors are you getting?
    enercheenhologoskaihoogosenprostontheonkaitheosenhologosnarchhnoogovkailogohnprvtonqeonkaiqeovhnologov

  8. #8
    New Member
    Join Date
    Aug 2008
    Posts
    12

    Default Re: Help with excel formula (copy data from one sheet to another when value matches)

    Thanks Aladin Akyurek, your solution does seem to work, but I'm not sure it is entirely suitable.

    Sheet 1 and 2 both have the same headings, although Sheet 1 has additional data also (Columns G onwards). The remaining data in Sheet 1 has no further relationship with Sheet 2.

    Sheet 1 will be an ongoing document and be added to regularly. I need Columns A - F to be the same 'heading' in both Sheets.

    Can this be done?? Particularly by just entering the reference number in Column A (rows 2 - about 500 specifically). I assume I can apply the same formula in every cell in Column A-F to derive the matched data?!

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    63,619

    Default Re: Help with excel formula (copy data from one sheet to another when value matches)

    Quote Originally Posted by BravoBravo View Post
    Thanks Aladin Akyurek, your solution does seem to work, but I'm not sure it is entirely suitable.

    Sheet 1 and 2 both have the same headings, although Sheet 1 has additional data also (Columns G onwards). The remaining data in Sheet 1 has no further relationship with Sheet 2.

    Sheet 1 will be an ongoing document and be added to regularly. I need Columns A - F to be the same 'heading' in both Sheets.

    Can this be done?? Particularly by just entering the reference number in Column A (rows 2 - about 500 specifically). I assume I can apply the same formula in every cell in Column A-F to derive the matched data?!
    If you have just a single record (a row of data) per employee, the setup will simply work.

    You can take care of the headers yourself for it's a one time operation.

  10. #10
    New Member
    Join Date
    Aug 2008
    Posts
    12

    Default Re: Help with excel formula (copy data from one sheet to another when value matches)

    Thanks Aladin Akyurek, I've played around and it seems to be working.

    What can I do with the '4' in cell C3 though - it causes me problems with formatting and adding additional rows of data...

Page 1 of 2 12 LastLast

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