Using an IF Formula with a combo box

    Hi,


    I have several worksheets in one file. Each worksheet has sales figures in it which relates back to the combo box in the first sheet which is the home page.


    In the home page i have a drop down combo box with over 20 sales reps names in it. These names represent individual locations and states (e.g. NNO, NSO NWE, NCN) (eg NNO -NSW North) and so on. (the information in one worksheet represents one sales rep)


    Each sheet shows sales figures for each sales rep for 2003, 2004, 2005 and 2006.


    When you select a sales rep from the combo box on the home page it should automatically retrieve the figures from the correlating worksheet and paste it into the relating cells on the home page


    i have acheived this using the IF formula however it will only allow me to do it for 6 sales reps and not 27.


    The formula I used is shown below;


    =IF(B3=1,NNO!D21,IF(B3=2,NSO!D21,IF(B3=3,NWE!D21,IF(B3=4,NCN!D21,IF(B3=5,NCS!D21,IF(B3=6,NHO!D21))))))



    Hope you can help!


    Thanks
    Regards


    Natalie

    Re: Using an IF Formula with a combo box


    Hi Natalie


    You should put the names in a column and lookup.


    EDIT: typo here!


    Put in A1: NNO
    in A2: NSO
    in A3: NWE
    in A4: NCN
    etc.


    Then, use:


    =INDIRECT(INDEX($A$1:$A$27,B3)&"!D21")


    Does this help you?


    Wigi

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

    Re: Using an IF Formula with a combo box


    I'd try to get all the info into one column on one worksheet:


    i.e. Cell A1 might be "=NNO!D21", Cell A2 could be "=NSO!D21", etc...


    Then, you can use "=INDEX(myRange, B3)" where myRange is that new column you created.


    If that's not an option, you could use "CHOOSE" instead of nested if's.


    i.e. =CHOOSE(B3, NNO!D21, NSO!D21, ...)

    Re: Using an IF Formula with a combo box


    But Im not sure i can do that because each rep the info is for 2003, 2004, 2005 and 2006, so if i put the dta all in one column how will it work?


    thanks

    Re: Using an IF Formula with a combo box


    Thanks I'll give it a go not sure if it will work though we'll see

    shall i send you a file of it?

    thanks- i'll let you know how i went

    Re: Using an IF Formula with a combo box


    Hi Nat ;)


    Quote from Natalie

    But Im not sure i can do that because each rep the info is for 2003, 2004, 2005 and 2006, so if i put the dta all in one column how will it work?


    If you have multiple years, it's better to use my approach. You only put sheet names in column A (e.g.). Use the INDIRECT function as I pointed out. Without knowing the structure of your file, if you have multiple years, just change the D21 accordingly, depending on the year.


    You can attach small part of your file here. Delete all unnecessary details / formatting.


    Also, mind the typo I corrected in my earlier post.


    Wigi

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

    Re: Using an IF Formula with a combo box


    Wigi.


    I will give it a go! Im trying to send you my file but i ahave to edit it because you acn only have 48K so what i send you will look nothing like the original file


    But i will try what you said and see if it works


    Thanks again!


    Speak soon

    Re: Using an IF Formula with a combo box


    Nat


    See attachment. The formula is put in a red background color. Remove the formatting and fill down to below and to the right as much as needed.


    Also, complete the list below with acronyms.


    This should work, but if not, ask back please.


    Cheers


    Wigi

    Re: Using an IF Formula with a combo box


    What can I say i am impressed "You're a Legend Wigi"


    Where did you learn all this?



    Seems to Work I haven't done it for all 27 items but I will fill in the rest as you said and will see if it works!


    This is so great because the formula I was using before I had to do it one at a time for each cell and couldn't copy over the formula- this makes it so much easier!!!!


    I'm going to get right on it I'll let you know how it turns out!


    Thanks so much for your help!!!!



    Nat

    Re: Using an IF Formula with a combo box


    Wigi,

    All is going well haven't finished yet but I have another question related to this.

    When I do the formula and drag it down and across, if my raw data has "0" (zeros in it) it will just come up as blank- how do I make the zeros come up and also how do I make all the zeros show up in red writing or red background?


    Thanks again

    Nat

    Re: Using an IF Formula with a combo box


    Quote from Natalie

    Is anyone on line Wigi did you get my msg?


    Hi Nat


    Users get answers usually extremely fast, so if it's 12 hours or so, it's unusual but certainly don't panic :)


    Actually, the small LED next to someone's nickname turns green when the user is online.


    About the zero's not showing up: normally they do in fact show up. But you (or someone else) turned it off. Check out Tools > Options > Display (don't know correct English term) > Zero values. There, click the small checkbox to turn it on.


    Wigi

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

    Re: Using an IF Formula with a combo box


    Nah something else- when i put the formulas in all works well and when i select a name their info comes up. but becuae i have for eg vic sales total nsw sales total australia total etc it has formulas in it ie NNO!C6+NSO!C6+NWE!C6 When i select for eg NSw sales total from the drop down box it brings up REF# in every cell???

    Re: Using an IF Formula with a combo box


    sorry i meant #REF! thats what comes up in every cell- i think becuae their are formulas in it its not grabbing the figure?

Participate now!

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

Register Yourself Login