Excel Experts? I need some advanced help..

Discussion in 'OT Technology' started by DatacomGuy, Jan 22, 2007.

  1. DatacomGuy

    DatacomGuy is moving to Canada

    Joined:
    Oct 14, 2002
    Messages:
    16,546
    Likes Received:
    0
    Location:
    Tampa, FL
    I consider myself an intermediate to advanced excel user, however I\'m struggling with something.

    I\'ve always wanted to create a form for sales purposes. In this workbook, there would be many worksheets with product PN\'s, descriptions, and costs. On the main form, when someone selects a manufacturer name from a drop-down box in A1, it automatically allows you to select a PN from the matching vendor worksheet in A2. Once the PN is selected, it automatically pulls up the description and cost and puts in their corresponding cells.

    Make sense? Any ideas on how to do this?
     
  2. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Not use Excel? Seriously - it can be done but why do people insist on using Excel as a database?

    You can use validation to limit the choices, but you have to have the information on the same worksheet. Or you can get into VBA and limit the choices thrrough coding. The actual lookup isn't that card (vlookup), just the limiting is stupid - at least in Excel. Now - get yourself in access and this is a piece of cake.
     
  3. deusexaethera

    deusexaethera OT Supporter

    Joined:
    Jan 27, 2005
    Messages:
    19,712
    Likes Received:
    0
    The man's got a point. Access is perfect for this kind of stuff.
     
  4. DatacomGuy

    DatacomGuy is moving to Canada

    Joined:
    Oct 14, 2002
    Messages:
    16,546
    Likes Received:
    0
    Location:
    Tampa, FL
    I don't have issues with going to Access. I know Access fairly well. Intermediate, not advanced..

    Best way to go abouts in Access?
     
  5. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Well start by making your tables - manufacturers, parts, customers, sales form, sales details:

    Sales

    SalesID
    CustomerID
    OrderDate
    ...

    Customer

    CustomerID
    Name
    Address1
    Address2
    City
    ...

    SalesDetail

    SalesDetailID
    SalesID
    PartID
    Quantity

    Manufacturers

    ManufacturerID
    ManufacturerName
    Address1
    Address2
    ...

    Parts

    PartID
    ManufacturerID
    PartName
    Description
    Price
     
  6. DatacomGuy

    DatacomGuy is moving to Canada

    Joined:
    Oct 14, 2002
    Messages:
    16,546
    Likes Received:
    0
    Location:
    Tampa, FL
    OK, done..
     
  7. DatacomGuy

    DatacomGuy is moving to Canada

    Joined:
    Oct 14, 2002
    Messages:
    16,546
    Likes Received:
    0
    Location:
    Tampa, FL
    BTW, I have all the manuf/part details in a spreadsheet already. Should I append to the tblParts table, or should I put each manufacturer in its own?
     
  8. cassetti

    cassetti New Member

    Joined:
    Nov 30, 2003
    Messages:
    252
    Likes Received:
    0
    Location:
    delaware
    Honestly - screw Microsoft products

    go to www.filemaker.com and download a trial of filemaker pro 8 or pro 8.5

    You will not regret getting filemaker pro - it is a cheap database that is AMAZINGLY easy to use! If you know how to do simple calculations in excel, and know the basic concepts of databases, filemaker pro is the way to go.

    I took up learning fmp at my current job- to take over for my boss since he had no time to work on our database. This program is a powerful WYSWYG editor for databases. It will take you 2 days to create exactly the database you need - and if you don't make it correctly - it takes a few seconds to fix the database.

    I highly recommends you at least download it and try it out - you'll be hooked

    I plan on buying a copy of filemaker mobile to put on my cingular 8525 phone - so i can create databases exactly how i want them to work.
     
  9. DatacomGuy

    DatacomGuy is moving to Canada

    Joined:
    Oct 14, 2002
    Messages:
    16,546
    Likes Received:
    0
    Location:
    Tampa, FL
  10. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Add in a ManufacturerID to the SalesOrderDetails.
    Use the form wizard to make a Sales Order.
    Then add in a subform for sales order details.
    Change ManufacturerID in the subform to a drop down box pulling data from the manufacturer table
    Change the PartID in the subform to a drop down box pulling data from the parts table
    Change the data source of that same box to limit the selection of the parts to manufactures specified in manufacturerID drop down. You can refer to it as forms!SalesOrder!SalesOrderDetailsSubform.form!ManufacturerID in the criteria.

    You may have to refresh the data source each time you change the manufacturerID.
     
  11. DatacomGuy

    DatacomGuy is moving to Canada

    Joined:
    Oct 14, 2002
    Messages:
    16,546
    Likes Received:
    0
    Location:
    Tampa, FL
    Wheeeew..
     
  12. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
  13. DatacomGuy

    DatacomGuy is moving to Canada

    Joined:
    Oct 14, 2002
    Messages:
    16,546
    Likes Received:
    0
    Location:
    Tampa, FL
    It\'s been a while! I\'m using Access 07 too, which I\'m not familiar with. I may need to go back to 03.
     

Share This Page