Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Oct 2003
    Location
    Vancouver, Washington, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cell linked to Access table (2000)

    I want to link a cell in an Excel spreadsheet to a simple (ID, Company name) Access table, so when I enter the ID in Excel, the company name appears. How do I do that?
    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Cell linked to Access table (2000)

    You can pull the Access table into Excel using Data | Get External Data | New Database Query..., then use a VLOOKUP formula to retrieve the company name associated with an ID.
    You can specify when the data in Excel will be refreshed - only manually, or every n minutes and/or when the workbook is opened.
    You can hide the worksheet with the data table if you like.

  3. #3
    Star Lounger
    Join Date
    Oct 2003
    Location
    Vancouver, Washington, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell linked to Access table (2000)

    Hans
    Thanks for the prompt reply. Please excuse my ignorance, but what are the actual steps for using VLOOKUP? When I skip that step, I get the whole Access table inserted into an Excel cell. What I want to do is type "3" (the Access key) into the cell and get "Publisher H" (from the Access table).
    Thanks.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Cell linked to Access table (2000)

    I hope you don't really mean that the entire Access table is dumped into a single cell.
    The first step (Data | Get External Data | New Database Query...) should result in having a copy of the Access table in a worksheet in Excel, say in Sheet2!A1:B100 (just for illustration purposes).
    On another worksheet, A2 is the cell in which you enter a key number such as 3.
    In B2 (the cell next to it), you have this formula:

    =VLOOKUP(A2, Sheet2!$A$1:$B$100, 2, FALSE)

    This will display the value from the second column in the linked table corresponding to the key in A2. If you want to suppress the #N/A you get if there is no match, use

    IF(ISNA(VLOOKUP(A2, Sheet2!$A$1:$B$100, 2, FALSE)), "", VLOOKUP(A2, Sheet2!$A$1:$B$100, 2, FALSE))

  5. #5
    Star Lounger
    Join Date
    Oct 2003
    Location
    Vancouver, Washington, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell linked to Access table (2000)

    Excellent! Many thanks. Works like a dream.

Posting Permissions

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