Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Aug 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Is there a better way to do this? (Excel 2002)

    I am trying to simplify the medical supply ordering for our small fire department. I have listed the most commonly ordered supplies from two suppliers. I want to enter a quantity in one column and let Excel figure out which is cheaper and place the quantity, description, and item number on a separate worksheet for the appropriate company, which will then be printed and faxed.

    I have been trying to use IF statements to do this, but have not figured out a good logical way to do it. I would appreciate any ideas.

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Is there a better way to do this? (Excel 2002)

    Look at <!post=this,284229>this<!/post> thread and the downstream thread. In your case the criteria would be the name or code fo the supply you are searching for. If this doesn't help, you might post a censored/simplified sample of the spreadsheet.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    New Lounger
    Join Date
    Aug 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is there a better way to do this? (Excel 2002)

    I didn't understand how that would apply to my situation. I think it left my limited realm of understanding. I am going to tinker with those commands to try to figure them out, but in the meantime, here is the spreadsheet, chopped down and cleaned.

    Thanks!

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Is there a better way to do this? (Excel 2002)

    Are the two suppliers which you want to compare "OPA/NPA" and "ET"? If so, there are are no common codes in your example showing that the supply class is the same. For example, if "NPA 30 FR" and "ET Tube, uncuffed 3.0" are the same thing, there will need to be some common code to identify them and used to compare them with. (Apologies if I'm not understanding this.)
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Is there a better way to do this? (Excel 2002)

    Hi,

    Try the attached. Enter the Quantity on the 'Order' sheet, and the appropriate entry will appear on whichever of the two others has the minimum pricing. Just hide the unwanted rows before printing. Actually hiding all the unwanted rows would make things considerably more complex, and would probably best be done using VBA.

    Note, too, that there's no error checking for the case where both suppliers have offered the same price - in that case, the order will appear on both sheets. If you need to control that, it would take a fair bit more work to generate an optimal solution.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. #6
    New Lounger
    Join Date
    Aug 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is there a better way to do this? (Excel 2002)

    OPA/NPA and ET are products, The other two sheets are named after the mythical companies. Each of the products listed down the left side are unique, NPA 30 FR is a nasal pharyngeal airway, size 30 french (for example). Moving along the same row as NPA 30 FR you will see unique item numbers, one for each company, as well as amounts and how many units are in a box, case or whatever.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •