Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2004
    Hampton, VA

    Unanswered: Reference like a database for keys to insert

    Okay, this sounds really strange but I have a Worksheet that follows a theme:
    HTML Code:
       Col1      Col2    Col3  Col4  Col5  Col6   Col7
    Country1   Region1   234   2397  City1 342A   3567
    Country2   Region2   342   3562  City3 500B   7455
    Country3   Region3   842   3621  City2 342B   1367
               Region1   500   3453  City1 532F   4275
               Region2   532   2356   ...
    Seems confusing, but:
    Col1 are Parent "National"
    Col2 are "Regional" (Col3 a reference number and Col4 the primary key)
    Col5 are "Local" (Col6 a reference number and Col7 the primary key)

    Why you might ask? Well when users double click cell in the "A" colum of all other worksheets, a user form pops and present them with a selection list from COL1 of the Sheet above. Once selected a nested IF statement will result the matches from COL2 and another IF statement will yield way to options from COL5. The result is then entered into the A cell with all three selection in a comma delimited format.

    I have the selection held as a variable and I now need to use it to "lookup" the corresponding "primary key". Meaning that I need to:
    Search All of the cells in Range A1:H200 to find the match (which will always exist) and then record the COL4 or COL7 result in a hidden field. So, if the users only selects a Region, then I need COL4 in cell "F" or if they select a Local, then I need COL7 in cell "F".

    I think that there is a better way of doing this, but I've go so many issues going on right now that I can't seem to think straight.
    Last edited by Adrianna; 09-10-04 at 09:30.

  2. #2
    Join Date
    Aug 2004
    Hampton, VA

    Smile Feeling Dumb

    OKay, there was a really easy fix for this. I created and named "List", then used VLOOKUP to find the match and pass back the primary key.

    So, Col2-Col4 became RegionList and then I was able to call on the primary by using =VLOOKUP(A2, RegionList, 3, False) which looks up the entry in Cell A2 in my RegionList and returns the corresponding value in col4 (which is the 3rd column in the Regionlist). False, merely states that there must be an exact match.

    Hope this helps someone else!

Posting Permissions

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