Updating list 1

[dje71183]

This is my first time doing something like this so please bear with me.

I am trying to set up a program using VB in Excel.

I have a master list of parts and prices.

Sub lists made up of parts and prices from the master.

Both the master list and sub lists on same worksheet.

I want to be able to update the master list then have the program update the sub list.

How can I have the program look for the same part in the master list to match it up with the sub list and enter the new price that I put in the master?

Make sense?

 

[joerd]

If you do a VLOOKUP or INDEX - MATCH combination, you don't need any VB at all. There are many good examples available, also in this forum.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[bridgeart]

Hi, dje71183, Joerd is completely right that you don't need VBA at all. Below is a simple example:

*** master list ***

1 2 3

A ID part price
B 1 p1 $12
C 2 p2 $13
.
.
.
Z 25 p25 $1

*** sublist ***

1 2 3 (column numbers)

ID VLOOKUP formula VLOOKUP formula



Formula =VLOOKUP(1,$B$1:$Z$3,2) will return name of part with ID equal to 1. Formula =VLOOKUP(1,$B$1:$Z$3,3) will return price of part with ID equal to 1.

You can set up your sublist in a similar format as the master list: ID in first column, and VLOOKUP formulas in the second and third columns to get you part name and price. First parameter of VLOOKUP formula would be a cell name in the first column - when you change the ID in the first column, correct part name and price will be displayed.

Hope this helps,
bridgeart

--

http://www.bridgeart.net

 

[pmover]

dje71183,

joerd is absolutely correct - no vba is required. furthermore, you may want to consider using what is termed "dynamic arrays" or "ranges". please see the following examples at:

http://www.ozgrid.com/Excel/DynamicRanges.htm

hope this helps!
-pmover

a star for joerd!

 

[FrancisL]

It would be much better in a database though. Access even has a template (Inventory Control) for what you want.