Consulting

Results 1 to 5 of 5

Thread: Referencing access data in excel

  1. #1
    VBAX Mentor
    Joined
    Aug 2011
    Posts
    353
    Location

    Referencing access data in excel

    I have an access table named cusips. The fields are cusip, maturity, coupon, and title. This table is linked to an excel sheet which contains the information for each cusip.

    See the attached excel document. In my attached excel doc, the first sheet is called data. This sheet is linked to my access table and houses all the data for each cusip. I enter the data here. The second sheet is called pulled from access. In cell B5, I would like to be able to enter a cusip (that exists in the access table) and when I enter that cusip in excel in that box, it pulls the info from access such as its title, maturity, and coupon. This is a really simple version of what I'm trying to get to so I'm making it very simple so you all can help me understand how to pull data.

    Thanks for the help!
    Attached Files Attached Files

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    You could just lookup the data sheet

    =VLOOKUP(B5,Data!A:D,4,FALSE)

    and so on
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Mentor
    Joined
    Aug 2011
    Posts
    353
    Location
    Well, the data in sheet one is set up in a very complicated way so using vlookup would not be easy. I pull the data into access to simplify it. So I would essentially like to create " a relationship" between an access table and an excel worksheet. So i would like to be able to type in a cusip in cell B5(see on excel doc sheet 2)...and have the title, maturity, and coupon pulled from access for that cusip and entered in B6, B7, and B8, respectively.

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Could be done with simple event code. Do you have a copy of the DB to test it on?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Mentor
    Joined
    Aug 2011
    Posts
    353
    Location

    DB attached

    Attached is the copy of the excel sheet. Since I can't attached two things, on sheet 2 in excel (data in access) I have copied my access table, which is named cusips, so you can see what it looks like. So I would like to be able to type in a cusip in cell B5 (in the pull from access sheet in excel) and essentially have a code that looks up the title, maturity, and coupon for that cusip from the data located in access. I know in theory I could just export the access database to excel and do a vlookup but that is not really the best option because things are much more complicated. I have just simplified them for now.
    Attached Files Attached Files

Posting Permissions

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