+ Reply to Thread
Results 1 to 27 of 27

IF & COUNTIF? Flagging up dependent equipment

  1. #1
    Forum Contributor
    Join Date
    12-30-2008
    Location
    Woking, England
    MS-Off Ver
    Excel 2007
    Posts
    197

    IF & COUNTIF? Flagging up dependent equipment

    Hi,

    A few more functions and hopefully I can put this spreadsheet to bed

    In my spreadsheet I have formulas highlighting equipment that has been missed, they are displayed in box L46:O54 in the take off. This is being worked out by formulas in the dependencies tab.

    I have selected 'DL3MVBL' in I5.

    The formulas in the dependency tabs mean that DL3SBL and DL3SUL are being flagged up.

    DL3MVBL - this is the software base build
    DL3SBL - this is a server license (and includes 10 intelligent device licences) and is needed if DL3MVBL is selected.
    DL3SUL - this is an intelligent device license and is needed per intelligent device if DL3MVBL is selected.

    However...

    I need the L46:O54 box to tell me exactly how many licenses I need? I have created a column J in the 'Data' tab and have either marked each device with a 'Y' or left it blank. This indicates whether it's intelligent. For Example, the sheet I will upload shows 50 intelligent devices. I need the L46:O54 box to indicate that I need 1x DL3SBL, and 40x DL3SUL.

    Many Thanks

    Sam
    Attached Files Attached Files
    Last edited by 3smees23; 05-27-2012 at 05:21 AM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: IF & COUNTIF? Flagging up dependent equipment

    So how are you getting that you need in this case: 1x DL3SBL, and 40x DL3SUL.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Contributor
    Join Date
    12-30-2008
    Location
    Woking, England
    MS-Off Ver
    Excel 2007
    Posts
    197

    Re: IF & COUNTIF? Flagging up dependent equipment

    Beacuse the DL3SBL contains 10x DL3SUL.

    So if I had 100 inteligent devices on my system and I selected the software base build (DL3MVBL ). The the prompt should be 1x DL3SBL, and 90x DL3SUL.



    If I have 9 inteligent devices on my system and I selected the software base build (DL3MVBL ). The the prompt should be just 1x DL3SBL (as it contains 10xDL3SUL)

    Sam

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: IF & COUNTIF? Flagging up dependent equipment

    Quote Originally Posted by 3smees23 View Post
    Beacuse the DL3SBL contains 10x DL3SUL.
    I am oblivious to how I would know that? Can you point me to the exact place I am to find that info?

  5. #5
    Forum Contributor
    Join Date
    12-30-2008
    Location
    Woking, England
    MS-Off Ver
    Excel 2007
    Posts
    197

    Re: IF & COUNTIF? Flagging up dependent equipment

    Hiya,

    There is nothing in the spreasheet that has that info. Can the formula not work out that if DL3MVBL has been selected than apply DL3SBL, count the 'Y' in column J (data tab) minus 10 (for the DL3SBL). That would provide me the exact amount of DL3SUL in the prompt box?

    Sorry if I haven't explain myself very well. Sometimes I think I'm being clear when obviously I'm not.

    Thanks for all you help so far NBVC

    Sam

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: IF & COUNTIF? Flagging up dependent equipment

    So for this sample workbook you posted, I count 242 "Y's" in column K of Data sheet...

    What are the actual numbers you want to see in L46:O54 as it stands?

    Also the 10 supposedly applies to DL3SBL... what about other selections, etc.. what would the "multiplier" be then?

    I am sorry, but I am thoroughly confused

  7. #7
    Forum Contributor
    Join Date
    12-30-2008
    Location
    Woking, England
    MS-Off Ver
    Excel 2007
    Posts
    197

    Re: IF & COUNTIF? Flagging up dependent equipment

    That's correct, so those 242 pieces of equipment all need 1x DL3SUL license each.

    As it stands I need the box to look like:

    DL3SUL SW913703092209 40
    DL3SBL SW913703092009 1

    When I select the software base license (DL3MVBL) I always need to apply the server license (DL3SBL). Hence having it assigned to the base build in the dependency tab.

    The server comes with 10 intelligent licenses (like a starter pack). so that 10 always needs to be taken into account, otherwise I would apply 10 too many licences.

    No matter how many intelligent devices are on the system, whether its 20 or 2000, I will always need 1 per intelligent device, then minus 10 off the total.

    So 20 would be:

    DL3SUL SW913703092209 10
    DL3SBL SW913703092009 1

    2000 would be:

    DL3SUL SW913703092209 1990
    DL3SBL SW913703092009 1

    Hope this makes more sense?

    Sam

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: IF & COUNTIF? Flagging up dependent equipment

    Please forgive me.. but I must be missing something obvious.... where is the 50 coming from exactly?

  9. #9
    Forum Contributor
    Join Date
    12-30-2008
    Location
    Woking, England
    MS-Off Ver
    Excel 2007
    Posts
    197

    Re: IF & COUNTIF? Flagging up dependent equipment

    Do you mean 40?

  10. #10
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: IF & COUNTIF? Flagging up dependent equipment

    I guess, it's the same thing...

    You said that if you have 50 intelligent units, subtract 10 to get 40... so where are those gotten from specifically in this case?

  11. #11
    Forum Contributor
    Join Date
    12-30-2008
    Location
    Woking, England
    MS-Off Ver
    Excel 2007
    Posts
    197

    Re: IF & COUNTIF? Flagging up dependent equipment

    Hiya, Try looking at this. hopefully this makes more sense
    Attached Files Attached Files

  12. #12
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: IF & COUNTIF? Flagging up dependent equipment

    Test this in E2:

    Please Login or Register  to view this content.
    Last edited by NBVC; 05-23-2012 at 12:15 PM. Reason: Updated Data sheet range

  13. #13
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: IF & COUNTIF? Flagging up dependent equipment

    Note:

    I updated the DATA sheet range in my formula above to cover all of the data in the Data sheet (I had it to row 100) for testing....

  14. #14
    Forum Contributor
    Join Date
    12-30-2008
    Location
    Woking, England
    MS-Off Ver
    Excel 2007
    Posts
    197

    Re: IF & COUNTIF? Flagging up dependent equipment

    It's pretty much there, the only problem I have found is that if you type 1 in M5, it increases the DL3SUL's to 657, it should stay at 656.

    I think I had it as an intelligent device before, but on the attached it isn't marked as intelligent in the Data tab.

    any suggestions?

    Thanks for all you help

    God only knows how one person can know so much about excel :-)
    Attached Files Attached Files

  15. #15
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: IF & COUNTIF? Flagging up dependent equipment

    Please tell me this is the one :praying:

    Please Login or Register  to view this content.

  16. #16
    Forum Contributor
    Join Date
    12-30-2008
    Location
    Woking, England
    MS-Off Ver
    Excel 2007
    Posts
    197

    Re: IF & COUNTIF? Flagging up dependent equipment

    Your prayers have been answered NBVC, it works!!! Thanks for you continued hard work.

    All the best

    Sam

  17. #17
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: IF & COUNTIF? Flagging up dependent equipment

    Great... since that worked and taking a look at it again, I think we can simplify the formula a bit.. there is no more need to limit the range in take-off to only the entries...

    Try instead:

    Please Login or Register  to view this content.

  18. #18
    Forum Contributor
    Join Date
    12-30-2008
    Location
    Woking, England
    MS-Off Ver
    Excel 2007
    Posts
    197

    Re: IF & COUNTIF? Flagging up dependent equipment

    S**t, there was a prob with the first one, just checked and its still happening on your refined formula. Type any number in O5 (not 521) and it turns the prompt to green and says no more DL3SUL's are required :-( I think this one might break you NBVC.

    PS check back a few formulas ago. im pretty sure this part worked fine
    Attached Files Attached Files

  19. #19
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: IF & COUNTIF? Flagging up dependent equipment

    What should we be seeing and why? Are we saying that as long as there is a DL3SUL requirement with Intelligent Units, then it should appear... ie. is the answer in this case always 521? What number in O5 should make the section go green?

  20. #20
    Forum Contributor
    Join Date
    12-30-2008
    Location
    Woking, England
    MS-Off Ver
    Excel 2007
    Posts
    197

    Re: IF & COUNTIF? Flagging up dependent equipment

    521 is completely right, what I was getting at was that if I type 1 in O5 the the prompt dissapears and the box turns green. Even though I'm 520 DL3SUL short on my system.

    It should only turn green when I have fullfilled all my dependancies ie. if i have allowed for all equipment that is needed.

    So...if i type 1 in O5 I need N46 to say 520.

  21. #21
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: IF & COUNTIF? Flagging up dependent equipment

    Okay, here we go...

    In Dependancies sheet... update formula in D2 to:

    Please Login or Register  to view this content.
    and in E2:

    Please Login or Register  to view this content.
    each copied down

  22. #22
    Forum Contributor
    Join Date
    12-30-2008
    Location
    Woking, England
    MS-Off Ver
    Excel 2007
    Posts
    197

    Re: IF & COUNTIF? Flagging up dependent equipment

    It works!! Thanks NBVC

  23. #23
    Forum Contributor
    Join Date
    12-30-2008
    Location
    Woking, England
    MS-Off Ver
    Excel 2007
    Posts
    197

    Re: IF & COUNTIF? Flagging up dependent equipment

    urggghh. I have noticed another problem that wasnt there last time. Its prompting the DL3SUL when I havent selected the DL3MVBL base software. All the other dependancies are working fine, so why would one particular row act different?

    :-(
    Attached Files Attached Files

  24. #24
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: IF & COUNTIF? Flagging up dependent equipment

    I apologize, this is getting confusing...

    Try updating formula in D2 of the Dependancies tab to:

    Please Login or Register  to view this content.

  25. #25
    Forum Contributor
    Join Date
    12-30-2008
    Location
    Woking, England
    MS-Off Ver
    Excel 2007
    Posts
    197

    Re: IF & COUNTIF? Flagging up dependent equipment

    Hiya,

    It works in essence, but could the DL3SUL's appear when the DL3MVBL base build is given a quantity of 1....i.e. once 1 is entered in I5 on the attached.

    This means it works in the same principle as the other dependencies. So that when the equipment is selected form the drop downs, dependent equipment doesn’t appear in the prompt box until the dropdown equipment has been given a quantity.

    Thanks NBVC. It's tantalisingly close

    Sam
    Attached Files Attached Files

  26. #26
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: IF & COUNTIF? Flagging up dependent equipment

    This is starting to feel like my own project now..... How much do I get paid?

    Ok, let's try this update in Dependencies D2:

    Please Login or Register  to view this content.

  27. #27
    Forum Contributor
    Join Date
    12-30-2008
    Location
    Woking, England
    MS-Off Ver
    Excel 2007
    Posts
    197

    Re: IF & COUNTIF? Flagging up dependent equipment

    Hmmm, chocolate coins? :-) I appreciate all your help NBVC. It's working!!!

    Regards

    Sam

+ 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