Posts by Boycie92

  • Number of Calendar Days between the first Start Date & the last End Date

    Re: Number of Calendar Days between the first Start Date & the last End Date


    Quote from Luke M;795335

    Could do this array:


    =MAX(IF(D2:D999<>"Exempt",C2:C999))-MIN(IF(D2:D99<>"Exempt",B2:B999))


    Remember to confirm using Ctrl+Shift+Enter, not just Enter. Side note, I'm not exactly sure where you data is, so may need to adjust range sizes. Just make sure all the ranges are the same size.



    Is there anyway to edit this formula so that if my table included blank rows (Start Date & End Date) to ignore them. At the moment the formula is using the blank values and giving me the wrong difference?


    Thanks,
    Boycie92

  • Auto populating drop down lists/cells based on conditions

    Re: Auto populating drop down lists/cells based on conditions


    Yes, if I came to a row/date in knew was exempt I would then overwrite the formula. There is also know way I couple predict what rows would be exempt.


    The table will be added to in time, so I would need the formula to populate in any new rows. Which I understand with the manual intervention isn't possible.


    I am assuming it wont work they way I want it to? I think I will stick to the drop down menus!


    I thank you fall all your time on this.


    Boycie92

  • Auto populating drop down lists/cells based on conditions

    Re: Auto populating drop down lists/cells based on conditions


    Hi,


    In example 4, I have giving you an idea of what I am expecting. The option in E9 has been selected using a drop down menu. In E10 I have entered =F9. I have then dragged the formula down into the other rows. When it has came to an exempt row (E12) I have manually entered this term. In E14 (highlighted red) I have manually entered in B because that is the last recommended stage.



    To be honest I don't know if this will work they way I want it to but I will rely on your expert advice! It may be simpler for me to stick to the dropdown list in Example 1?


    Thanks,
    Boycie92

  • Auto populating drop down lists/cells based on conditions

    Re: Auto populating drop down lists/cells based on conditions


    Hi AliGW,


    You logic is spot on.


    That row is exempt because in certain cases we want to record the data but not actually use it in any other capacity (Crazy I know!). In the actual real life scenario it makes sense :)


    Hence the reason why I need a solution that sees and uses the last recommended option that is not exempt.



    Thanks,
    Boycie92

  • Auto populating drop down lists/cells based on conditions

    Re: Auto populating drop down lists/cells based on conditions


    Hi,


    Initially the drop down box was used for the entire column (E) see example 1. So if I Place A in E9 and it recommended B in F9 I would place B in the next row (E10) using the drop down menu.


    The solution I want is to automate this but the exempt part is confusing me.


    I have used the drop down box for spelling reasons. Spell it wrong and column F won't work correctly (As this is a sample if have replaced these terms with letters)


    Thanks,
    Boycie92

  • Auto populating drop down lists/cells based on conditions

    Re: Auto populating drop down lists/cells based on conditions


    Hi AliGW,


    sorry I think I could have made this bit clearer.


    In example 2 the only way I could get the cells (E) to auto populate was to remove the data validation and use the =F9 formula.


    If I just let the formula runs its course without manual intervention. I get the scenario in Example 3. The formula see exempt within F12 and my current solution then populates E13 with exempt (not what I want)


    Is their anyway to write a formula that then looks at the last recommended outcome (F11)and puts that in its place (E13)?

  • Auto populating drop down lists/cells based on conditions

    Hi,
    I am wondering if someone can help.


    I have attached a sample spreadsheet.


    Tab Example 1 is what I currently use but I am looking for a solution that will provided me with the results seen in Example 2.


    Essentially I only want to enter the condition once in E9 (drop down list). The formula will look at the recommendation in F9 and auto populate this in E10 and so on.


    I have entered =F9 into E10 and then dragged the formula down. The problem I am having is when it comes to my “exempt” condition.


    If I have this entered I want a formula that is able to see the last recommended condition used and insert that instead.


    In Example 2 the condition in E12 is exempt and in F12 the recommendation is exempt. I have then manually entered B into E13. As B was the last recommendation (F11)


    Ideally I would like to keep my drop down menus but any solution would be great.


    Thanks in advance,
    Boycie92

  • Sequence Formula help

    Re: Sequence Formula help


    Strange. I have the issue with the old last row and not the new one. Please see below:


    G9 =IF([Condition]<>"Exempt",COUNTIF($E$9:E9,E9),0)
    G10 =IF([Condition]<>"Exempt",COUNTIF($E$9:E10,E10),0)
    G11=IF([Condition]<>"Exempt",COUNTIF($E$9:E11,E11),0)
    G12 =IF([Condition]<>"Exempt",COUNTIF($E$9:E12,E12),0)
    G13 =IF([Condition]<>"Exempt",COUNTIF($E$9:E15,E13),0)
    G14 =IF([Condition]<>"Exempt",COUNTIF($E$9:E14,E14),0)


    if it was to insert another row (15) it would look like this:


    G14 =IF([Condition]<>"Exempt",COUNTIF($E$9:E15,E14),0)
    G15 =IF([Condition]<>"Exempt",COUNTIF($E$9:E15,E15),0)


    See how whenever I enter a new row the formula in the previous last row changes? this is the issue I am having. I don't wait it to change.


    Thanks,
    Boycie92

  • Sequence Formula help

    Re: Sequence Formula help


    Apologies for the massive delay. Please see that attached example.


    I have added in a new row and you can see in G13 that the formula has changed to this: =IF([Condition]<>"Exempt",COUNTIF($E$9:E14,E13),0) when it should be this =IF([Condition]<>"Exempt",COUNTIF($E$9:E13,E13),0)


    It seem to start referencing the new row that's been added



    Thanks,


    Boycie92

  • Sequence Formula help

    Re: Sequence Formula help


    Thank you for taking the time to help me with this.


    I don't think i have been very clear about my problem so i apologies for that.


    Essentially i need the formula to count each instance of a term,


    So if A appears once (Cell F9) then in cell G9 it should say 1
    If B appears once (Cell F10) then in cell G10 it should say 1
    If C appears in Cells F11 & F12. In Cells G11 it should say 1 and in cell G12 it should say 2.


    The sequence should restart once a new option has been selected.


    I hope this helps make it a bit clearer! Sorry Again,


    Boycie92

  • Sequence Formula help

    Re: Sequence Formula help


    My apologies,


    This assumes i have used the solution correctly. The named range only applies to one option (cell F9) . So if i have five terms in column F, the first one (F9) would be used.


    The count if part would then only count the number of instances for the term in cell F9.


    Again this is based on the way i have used the solution, if i have applied it wrong, then please let me know.


    Thanks,
    boycie92

  • Sequence Formula help

    Hi,


    I was wondering if someone could help.


    I have this formula:


    =IF([@[Stage*]]<>"Exempt",COUNTIF($F$9:F9,F9),0)


    It has been used to count the number of instances per term used in column F.


    I have a table the problem is that when i add in new rows the formula in the row above alters and says something like this


    =IF([@[Stage*]]<>"Exempt",COUNTIF($F$9:F11,F10),0)


    Is there any way to stop this from happening?


    Thanks,
    Boycie92