MS Excel Help!

Sledgehammer70Sledgehammer70 California Icrontian
edited January 2008 in Science & Tech
Here is what I am trying to do. I am not sure if Excel can do it but I was sure it could.

I am trying to create a simple chart that tracks inventory levels of items coming in and out in one file. The first Sheet would have the actual name of the products and a qty count of those items. The 2nd page would have a In/Out check sheet of the items and would have the qty of items coming in and out. The goal is to have a drop down list of the items from sheet 1 to appear on sheet 2 so I can select which items are coming in and out and have the qty's auto update on sheet 1.

If anyone has the time to help me out I would greatly appreciate it.

Comments

  • kryystkryyst Ontario, Canada
    edited January 2008
    It can be done. But because you are essentially create a x=x+y style loop by updating your inventory. You'd need to create a VB macro linked to a button so that when you clicked on the button it made the change. Otherwise you'd end up in a loop every time you open the sheet.

    This sort of thing would be easier to do in Access.
  • Sledgehammer70Sledgehammer70 California Icrontian
    edited January 2008
    I was thinking... what if I put the list in random cells down the line on Sheet 2? and than just refrence those items to sheet 1? I guess the main question I have now is how do I get the In/Out QTY of items on sheet 2 to auto update the QTY's in my main list? or how do you tie a number to the item you select in the drop down list?
  • ThraxThrax 🐌 Austin, TX Icrontian
    edited January 2008
    Use access.
  • Sledgehammer70Sledgehammer70 California Icrontian
    edited January 2008
    I don't have access... and my supervisor wants it in Excel.
  • edcentricedcentric near Milwaukee, Wisconsin Icrontian
    edited January 2008
    In excel you may want to do it on three sheets, start, in/out, current.
    You can use the data validation to get your drop down list.
    Go to the first page with the list. Highlight it. In the box on the left above the worksheet give it a name. Go to where you want to use it and data, validation, list, and then enter "=name" for the source.
  • kryystkryyst Ontario, Canada
    edited January 2008
    The only way you can get your in/out idea to work is by programing in a VB script to a macro button. The macro then does the calculation and updates the page. That IS the only way. Spreadsheets are auto calculating so you can't create the type of format you want because it'll create a loop there is no other way around it. Unless you just want to have a static value that they'd manually change so if you open up the sheet and it has a stock value of 9 and they change that they just retype in 8 for example. If you want it to be 'click' driven it's got to be a macro no exceptions.
  • Sledgehammer70Sledgehammer70 California Icrontian
    edited January 2008
    well how do you create the macro for this?
  • kryystkryyst Ontario, Canada
    edited January 2008
    I personally don't know - I know how it would work. But I'm no good with the VB, that's why I use access for this kinda thing :)
Sign In or Register to comment.