Referencing access data in excel

Printable View

03-08-2012, 03:46 PM
Klartigue

1 Attachment(s)

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!
03-08-2012, 05:41 PM
Bob Phillips

You could just lookup the data sheet

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

and so on
03-09-2012, 12:33 PM
Klartigue

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.
03-12-2012, 05:38 PM
Bob Phillips

Could be done with simple event code. Do you have a copy of the DB to test it on?
03-13-2012, 07:18 AM
Klartigue

1 Attachment(s)

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.