Linking cells in different worksheets


G

Guest

I am trying to link a cell in one worksheet with a particular cell in another
worksheet but it has to meet a certain criteria. I am pulling into excel on
worksheet one my database from my business inventory with all of our prices.
On the other worksheet pages I want to create price sheets for various
customers. I want to link to certain products on worksheet one and pull the
correct pricing to the other worksheets. However, as our inventory changes,
the actual "row and column reference" for the item will not always be the
same. I want to link the information by a "search" field. For example, I
want worksheet 2 to look at worksheet 1 and find in column # 2 the
information for the product with the number 15. Column 2 will contain all
the numbers for our inventory products. I want the first line of my price
sheet on worksheet 2 to be the product # 15 from worksheet # 1. This product
# 15 may move around on page one as I refresh my data each week with new
pricing information. I always want the #15 data to show up on the SAME line
in the price sheet on worksheet 2. How do I reference this to make this
happen correctly and not mess up my price sheets each week. Thanks!
 
Ad

Advertisements

P

Pete_UK

Use VLOOKUP - it will search your second sheet for the product number
you specify and then allow you to return data from a column to the
right of the product number - something along the lines of:

=VLOOKUP(A2,Sheet2!A$1:F$100,3,0)

A2 (on sheet1) will contain the value you want to find (eg 15)
Sheet2!A1:F100 is the data table - adjust references to suit
3 means bring the data from the third column of the table (i.e. column
C)
0 (or FALSE) means look for an exact match

You can put the formula in B2, and copy down if you want to search for
other product numbers in column A.

Hope this helps.

Pete
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top