+ Reply to Thread
Results 1 to 7 of 7

Excel 2007 : DMIN Help please!

  1. #1
    Registered User
    Join Date
    05-01-2012
    Location
    Albuquerque, NM
    MS-Off Ver
    Excel 2007
    Posts
    18

    DMIN Help please!

    Hey all,

    I am tweaking a spreadsheet that needs to look up the minimum number of a list of numbers that belong to a particular item and number. For example:

    Bottle # Drug Code Drug Name
    1292 BUP Buprenorphine 0.3mg/ml
    1293 bup Buprenorphine 0.3mg/ml
    1294 bup Buprenorphine 0.3mg/ml
    35 BupR Buprenorphine 0.3mg/ml

    I am using the Dmin function to do this as the lowest number must be tied to the drug name (so I can detect the lowest bottle number of buprenorphine, or the lowest bottle of another drug).

    It works fine but there is a problem when another drug code containes what I am searching for. In the example above, my dmin is looking up the lowest bottle number for "BUP", and it returns "35", I assume because BupR containes "BUP". Is there a way to make this specific for what I am looking for?

  2. #2
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,286

    Re: DMIN Help please!

    Try using

    ="=BUP"

    in the criteria field

  3. #3
    Registered User
    Join Date
    05-01-2012
    Location
    Albuquerque, NM
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: DMIN Help please!

    I am compiling all the stuff into an inventory sheet and need it to be changeable. Is there a way to do that? Here is my line:
    =DMIN(Table2[#All],"Bottle #",'Drug Log'!B2:B3)

    When I type in this I get an error message. Where does the ="=BUP" go?
    =DMIN(Table2[#All],"Bottle #",="=A5")

    Appreciate the help. Still kind of green when it comes to database functions.

  4. #4
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,286

    Re: DMIN Help please!

    Somewhere you should have the function criteria, which would be the heading Drug Code in one row and the value that I gave in the subsequent row. Assuing these are in M1:M2, you function then is

    =DMIN(Table2[#All],"Bottle #",M1:M2)

    I had assumed you were already using it in this manner, and so you only needed to replace the criteria value with what I gave you.

  5. #5
    Registered User
    Join Date
    05-01-2012
    Location
    Albuquerque, NM
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: DMIN Help please!

    Bob, I appreciate all your help, and I am closing in on it!

    I understand that in the criteria section of my formula I have to have ="=BUP". That fixes my problem if I want to create a report for BUP, but what if I want to do it with another variable, say, ="=MOR"?

    My line is: =DMIN(Table2[#All],"Bottle #",'Drug Log'!B2:B3)
    B3 is a cell with a value linked to another cell that has a pull down list of all the drugs the user has inputted into the spreadsheet (itself another table). Is there a way to change the cell with the criteria in it, currently ="=BUP" to ="=MOR" when another drug code is selected? I tried inputting ="=B4" but it just gives me "=B4" in the criteria.

    The other idea I just had is whether there is a way to generate that criteria statement based on input from another cell. If I enter "MOR" in cell A1, can I get Excel to generate ="=MOR" in cell A2? I think this would work too, and probably be a lot easier. Thanks!
    Last edited by mosswood17; 05-02-2012 at 11:05 AM. Reason: Another idea on how to go!

  6. #6
    Registered User
    Join Date
    05-01-2012
    Location
    Albuquerque, NM
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: DMIN Help please!

    Quote Originally Posted by mosswood17 View Post
    The other idea I just had is whether there is a way to generate that criteria statement based on input from another cell. If I enter "MOR" in cell A1, can I get Excel to generate ="=MOR" in cell A2? I think this would work too, and probably be a lot easier. Thanks!
    I just figured it out!!!!!!!!! The line I am using is: ="="&A5. By concatenating the text I can change it to whatever the target cell is updating my criteria whenever a new drug is chosen! Thanks so much for your help! I really love working with Excel! Someday I will figure out Macros, but this place looks to be the place to do that.

  7. #7
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,286

    Re: DMIN Help please!

    Sorry I wasn't around at the end, I was mowing my lawn. Glad you sorted it though, and you got it right

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1