Using Excel to 'map' two databases

[Uncle_heFTy]

Version: 2004
Operating System: Mac OS X 10.4 (Tiger)
Processor: Power PC

My problem is one that means I have two databases - not related to each other - the first has 1,000 4-digit numeric codes each linked to an alpha description of an outlet type, the second contains 9,500 8-digit codes again each with an alpha-based product description. The task is to create a query form (I don't have access to 'Access') that one could enter either an outlet code or description from database 1 and then this would give me a drop down menu of the possible database 2 codes and descriptions from which to choose.

I have tried the index and match functions but cannot get them to work with this task. Can anybody give me some assistance. I could put up two extracts from each database on a web page if that would help.

Aargh!

Uncle heFTy

 

[Jim Gordon MVP]

Version: 2004
Operating System: Mac OS X 10.4 (Tiger)
Processor: Power PC

My problem is one that means I have two databases - not related to each other - the first has 1,000 4-digit numeric codes each linked to an alpha description of an outlet type, the second contains 9,500 8-digit codes again each with an alpha-based product description. The task is to create a query form (I don't have access to 'Access') that one could enter either an outlet code or description from database 1 and then this would give me a drop down menu of the possible database 2 codes and descriptions from which to choose.

I have tried the index and match functions but cannot get them to work with this task. Can anybody give me some assistance. I could put up two extracts from each database on a web page if that would help.

Aargh!

Uncle heFTy

Hi,

Create a workbook that has two worksheets:
4-digit codes
8-digit codes

You don't say what format the data is in now, but put your data into the
appropriately named sheets. Save your file, and this will become your
data source.

Next, head to this URL to learn how to use Excel as a relational database:
http://www.agentjim.com/MVP/Excel/RelationalOffice.htm

Pay attention to the parts about creating parameter queries. That's how
you can build your input forms to make on-they-fly queries. You don't
need Access to make SQL queries in Excel, but you will need to buy an
ODBC driver from Actual Technologies for about $30.

-Jim