# 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

Dear Wigi

Here is a simplified version of my file- see attached doc. I dont have all the work sheets in it- and i have only done the formula in the columns in yellow- hope it helps you to understand my problem!

Many Thanks

## Files

• 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

## Files

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

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

Are you online Wigi?

can anyone else help?

Thanks!

• Re: Using an IF Formula with a combo box

BUMP
Is anyone on line Wigi did you get my msg?

Rgds

Nat

• Re: Using an IF Formula with a combo box

Bump

• 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

Oh, about the custom formats (red font etc.), go to this link at Ozgrid.

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

Hi Wigi,
thanks for the tip about the zeors it worked!

• Re: Using an IF Formula with a combo box

But i have another issue

• 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!