3. MS XL question

MS XL question


  • I need to figure out how to automatically enter data into cells on one worksheet from another based off of a product number. I have a product master sheet that is regularly updated that I need to call the data from.

    So when I enter my product number in c13 on my sheet I want it to look up that number from the master sheet in column b. from there it needs to fill in b13 with data from column a from the master sheet. Likewise I would need g13 to get the data from d on the master and j13 to get the data from e on the master. They all have to be from the same row that corresponds with the original product number.

    I appreciate any help I could get figuring this out. Thanks!


  • Thanks Kall. Been busy at work so haven't had much of a chance to try this yet(yes this is for work). I'm thinking there might be a little more to it yet. I might have to use hlookup as well with some of my cell once I get correct product number via vlookup. This has been helpful. Now just to get enough time during my day to mess around with it a bit.


  • I didn't mean a chart like a graph. I meant a table. Mea culpa. I assume you have something like for your product listing (let's pretend the sheet is named "Products"):

    Product #          Product Name
---------          ------------
100                Pencil
101                Paper
102                Eraser
```And on your other sheet, let's say this one is named "Orders":

    Ordered Prod # Ordered Prod Name

    101

    
The formula we want in B3 would be:

    =VLOOKUP(A3, Products!A1:B5, 2)

    
So it'll grab the 101, find what row it is on the Products table, and return back "Paper".

Another tip to make it even easier, I would suggest taking your Products table and assign a Name to it. Then instead of writing out "Products!A1:B5" in your formulas, you can just write the Name you assigned.

  • Benji I messed around with that formula a bit. I couldn't get it to work.

    Kall I haven't had a chance to play around with yours but from looking at the link it might be a step in the right direction. The only thing is I am not using a chart.


  • VLOOKUP is a great function that allows you to lookup a value on a chart, and return a value from a different column in the same row on the chart.

    http://office.microsoft.com/en-us/excel-help/vlookup-HP005209335.aspx

  • M
    Legion

    Try =(SheetName!cell)

    Example (this would be thrown in the c13 cell): =(MasterSheet!b13)

    Let me know if that's the right direction.