×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Updating list

Updating list

Updating list

(OP)
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?

RE: Updating list

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.

RE: Updating list

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

--
www.bridgeart.net

RE: Updating list

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!

RE: Updating list

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources