Announcement

Collapse
No announcement yet.

Linking Excel worksheets in a one-many relationship

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Linking Excel worksheets in a one-many relationship

    Anyone know if there is a way of linking two worksheets together in a one-many relationship based on row number?

    Trying to use Excel to store data in a one-many relationship (Header/Transaction) tables with each table in a seperate worksheet. Question is, apart from copying / paste link to actual fields, how do I relate the two tables together.

    Example attached but that uses paste link to create the relationship. Test1.xlsx
    Attached Files
    Last edited by Ossian; 11th December 2017, 16:56. Reason: Removed invalid link

  • #2
    You can simulate database relationships with VLOOKUP (or, I suppose, HLOOKUP) but please remember Excel is not a database engine
    Lots of information online but roughly:
    In foreign key table, VLOOKUP(foreign key, range with primary key, column to look up, column to return)
    Tom Jones
    MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
    PhD, MSc, FIAP, MIITT
    IT Trainer / Consultant
    Ossian Ltd
    Scotland

    ** Remember to give credit where credit is due and leave reputation points where appropriate **

    Comment


    • #3
      Originally posted by Ossian View Post
      You can simulate database relationships with VLOOKUP (or, I suppose, HLOOKUP) but please remember Excel is not a database engine
      Lots of information online but roughly:
      In foreign key table, VLOOKUP(foreign key, range with primary key, column to look up, column to return)
      Yea, I thought about VLookup but that seemed more suitable to a simple small lookup table rather than something as continuous as a Transaction Header table.

      If we imagine that Header records are identified by their row number, then Transaction records effectively have a HeaderId (Header.Row) field. Question is whaty is the best way of implementing this in practice.

      I have considered that Excel is not a database but this sort of database of transactional values is not idealy suited to Access or other databases either.

      Comment


      • #4
        I really don't see any difference, apart from performance, in using VLOOKUP with large or small tables. Implementing the HeaderID field would be an arbitrary incrementing value.

        IMHO, a transactional structure as you describe is ideally suited for implementation in SQL Server and Access would get away with it better than Excel would - although I do not know exactly what data you are dealing with.

        Can you please remove or edit the link in your first post as it does not go to an example, and another member of staff has already flagged it as possible spam.
        Tom Jones
        MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
        PhD, MSc, FIAP, MIITT
        IT Trainer / Consultant
        Ossian Ltd
        Scotland

        ** Remember to give credit where credit is due and leave reputation points where appropriate **

        Comment


        • #5
          Originally posted by Ossian View Post
          Can you please remove or edit the link in your first post as it does not go to an example, and another member of staff has already flagged it as possible spam.
          The link is supposed to point to:
          http://associateddata.co.uk/Test/Test1.xlsx
          Unfortunately the website seems to cut-off everything after the domain name for some unknown reason! I've tried putting it back but after saving the message it is gone again.

          Edit:
          Anyone know how to remove the duff link in the original message?
          Last edited by Nick_C; 10th December 2017, 21:11.

          Comment


          • #6
            You could EDIT the original post and delete what you don't want to keep. Since you have 2 links in that post and both of them open up, which one is the duff one?
            1 1 was a racehorse.
            2 2 was 1 2.
            1 1 1 1 race 1 day,
            2 2 1 1 2

            Comment


            • #7
              Originally posted by biggles77 View Post
              You could EDIT the original post and delete what you don't want to keep. Since you have 2 links in that post and both of them open up, which one is the duff one?
              Not sure why both are working for you, for me the second one just links to the domain not the actual spreadsheet.

              Unfortunately editing the message and removing the attachment (now the second link) fails when saving with error 'Please enter a valid URL', so seemingly no way to remove that duff link.
              Last edited by Nick_C; 11th December 2017, 15:13.

              Comment


              • #8
                Edited second link to "localhost" - AFAIK there is no way of removing it
                Tom Jones
                MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
                PhD, MSc, FIAP, MIITT
                IT Trainer / Consultant
                Ossian Ltd
                Scotland

                ** Remember to give credit where credit is due and leave reputation points where appropriate **

                Comment


                • #9
                  Damn, each time I look at the post something has changed. My apologies Nick_C but when I posted my answer above all that was I saw was a simple edit. Sorry if I have caused any confusion. I think the Phuckup Phairy has paid this thread a visit, waved their buggered (see what I did there) wand and added to it a ............................ wait for it ............................. PHEATURE! Click image for larger version

Name:	Aaaaaa.gif
Views:	13
Size:	5.1 KB
ID:	515401

                  1 1 was a racehorse.
                  2 2 was 1 2.
                  1 1 1 1 race 1 day,
                  2 2 1 1 2

                  Comment

                  Working...
                  X