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

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


    Sorry, Boycie, I'm not quite clear what you are asking. Very simply, in example 2, are you wishing to override Exempt in column E or column F? And which of the two columns should the override come from?

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

    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)?

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


    I'm really sorry, but I just don't get this. What are your expected/required outcomes in E and F (all rows)? What is the purpose of the drop-down box if you aren't going to use it? I am REALLY confused here!!! And is this really the best way of doing column E if it doesn't give the results you want?


    =IF(AND(AND(G12<3,D12<10),[Condition]="A"),"A",
    IF(AND(AND(G12<3,D12>=10),[Condition]="A"),"B",
    IF(AND(AND(G12>=3,D12<10),[Condition]="A"),"B",
    IF(AND(AND(G12>=3,D12>=10),[Condition]="A"),"B",
    IF(AND(AND(G12<3,D12<10),[Condition]="B"),"B",
    IF(AND(AND(G12<3,D12>=10),[Condition]="B"),"C",
    IF(AND(AND(G12>=3,D12<10),[Condition]="B"),"C",
    IF(AND(AND(G12>=3,D12>=10),[Condition]="B"),"C",
    IF(AND(AND(G12<3,D12<10),[Condition]="C"),"C",
    IF([Condition]="Exempt","Exempt","N/A"))))))))))

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

    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

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


    OK - that's a bit clearer. Thanks!


    So, in E10 copied down:


    =IF(F9="Exempt",F8,F9)


    This will work as long as you don't get exempt twice running - is that likely?

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

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


    Hi,


    Thanks I'm glad I was able to make that a bit clearer.


    Yes its likely there will be multiple exempts and the table may get pretty big


    thanks,
    Boycie92

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


    OK - then I need you to provide a larger and more realistic set of sample data. At the moment, that which you have provided does not result in any exempt entries in column F.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

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


    Thanks. I'll have a look!

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

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


    OK. Let me see if I understand this:


    1. We start by selecting a value from a drop-down in E9.
    2. F9 is populated from its formula.
    3. E10 and onwards are populated by taking the lest recommended value in F (in this case F9).


    Have I got this right so far? If so, why is E12 exempt and not B if these are the results you WANT? If they are NOT the results you WANT, then what are?

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

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


    I'm afraid I have run out of time today. Maybe someone else will pick this up before I'm back again in the morning. I hope you find a solution.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

    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

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


    OK - so could you now please give me a manually mocked up version of what you want to see.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

    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

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


    OK, well the main problem is this: you can't have both formula and manual entry in E, so are you expecting this column to be manually overridden by operatives? Will this table be added to in time? In other words, do we need formulae in place in blank rows below those you have already entered? Because if the formula in the bottom row of E is overridden, there will be no formula in the next new row added in that column. I think you might need to rethink this. I hope you get what I mean. Getting a formula that works isn't the problem: it's dealing with the operative intervention.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

    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

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


    No worries! There is a way, but it would involve adding an extra column. Interested?

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login