none
Can I create relationships between 2 sheets? RRS feed

  • Question

  • Hi,

    If I have two sheets one sheet with catalog number and description second sheet with catalog number and price and I would like to import the price from sheet 2 to 1 (catalog number is primery key). Is there a way to define relationship between two sheets (instead of build a search function or use the vlookup function)?

    Many thanks,

    Guy


    Guy Zommer
    Friday, October 14, 2011 8:18 AM

Answers

  • Hi,

    If I have two sheets one sheet with catalog number and description second sheet with catalog number and price and I would like to import the price from sheet 2 to 1 (catalog number is primery key). Is there a way to define relationship between two sheets (instead of build a search function or use the vlookup function)?

    Many thanks,

    Guy


    Guy Zommer


    You cannot indicate an intrinsic relationship between the 2 sheets (as you might with 2 tables in Access).  The only way to do what you want is through a formula (VLOOKUP being one example) or a SQL query (which you can create through VBA or let Excel do the heavy lifting through the MS Query tool).  For an intro to the latter see

    Building and using a relational database in Excel (with a little help from MS Query)

    http://www.tushar-mehta.com/excel/newsgroups/rdbms_in_excel/index.html


    Tushar Mehta (Technology and Operations Consulting)
    www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)
    Microsoft MVP Excel 2000-Present
    Friday, October 14, 2011 3:49 PM
  • Since you are trying to build a query out of two sheets you can try using ADO in Excel

    http://support.microsoft.com/kb/257819

    Cheers

    Shasur

     

     


    http://www.vbadud.blogspot.com http://www.dotnetdud.blogspot.com
    Sunday, October 16, 2011 4:10 PM

All replies

  • Hi,

    If I have two sheets one sheet with catalog number and description second sheet with catalog number and price and I would like to import the price from sheet 2 to 1 (catalog number is primery key). Is there a way to define relationship between two sheets (instead of build a search function or use the vlookup function)?

    Many thanks,

    Guy


    Guy Zommer


    You cannot indicate an intrinsic relationship between the 2 sheets (as you might with 2 tables in Access).  The only way to do what you want is through a formula (VLOOKUP being one example) or a SQL query (which you can create through VBA or let Excel do the heavy lifting through the MS Query tool).  For an intro to the latter see

    Building and using a relational database in Excel (with a little help from MS Query)

    http://www.tushar-mehta.com/excel/newsgroups/rdbms_in_excel/index.html


    Tushar Mehta (Technology and Operations Consulting)
    www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)
    Microsoft MVP Excel 2000-Present
    Friday, October 14, 2011 3:49 PM
  • Thanks
    Guy Zommer
    Sunday, October 16, 2011 4:50 AM
  • Since you are trying to build a query out of two sheets you can try using ADO in Excel

    http://support.microsoft.com/kb/257819

    Cheers

    Shasur

     

     


    http://www.vbadud.blogspot.com http://www.dotnetdud.blogspot.com
    Sunday, October 16, 2011 4:10 PM