Working With Microsoft Office Database and Macros

[Omkar™ 0]

I've not been very keen on doing a PhD in Microsoft® Office Applications, but when it comes to using Excel for more complex processes, its much more than just deriving a formula for adding 2 cells! One of the engineers in my Dad's office was driven nuts by 'the way things work around here'!

 

They sell carbides and similar industrial tools and like everyone, they've got a pricelist and stock list. Now, the not-so-anticipated part of the available data and resources is the way it is presented. Here's what the dilemma is all about:

The pricelist contains Product ID of particular inserts/carbides (which is a 5 digit pure numeric) AND the Grade corresponding to that carbide. These are just 2 of the many attributes associated with each carbide type.

The stocklist contains the same Product ID, but not the Grade.

Now, when the engineers does his job, he's got to include the Grade in the stocklist he presents to the customer. And he's sitting all day there cascading 2 excel windows and simply adding a new column in the stocklist for the Grade and manually looking up the Grade corresponding to each Product ID in the pricelist, copying it to the right cell in the stocklist.

 

We were looking for something that would create a database of the Product ID and the corresponding Grade, and a macro that would look up the data and append it to the required document. Now is this possibleusing the (seemingly vast) resources from Microsoft® Office ~ if yes, can somebody please guide me into simplifying this cumbersome task? We'll be using Microsoft® Office XP Professional Edition.