IF a cell contains text from a drop down list, then

**MattHamby** · 12-03-2011, 01:12 AM

Hello,

I am having trouble getting the IF function to work! I am trying to to input numbers into a cell automatically when a certain cell has a text input selected from a list that I have set up using cell validation. Here is a copy of what I have tried so far, once I get this to work, there will be 10 different selections, and I will have another IF function in an adjacent cell with about 34 nested IF statements.

=IF(K3="Overhead","0",IF(K3="Preliminary","1",IF(K3="Production","2",IF(K3="Construction","3"))))

I have tried this several ways, without the quotation marks, and with brackets and so on, I am tired and stumped - Please, HELP!!!!!!!

I can load a copy of my spread sheet if that will help, I am also using Excel 2003.

Thanks,

Matt

**vlady** · 12-03-2011, 02:13 AM

Hello Matthumby
you cannot have 34 nested if statements,,
my advice is use vlookup instead.
in your if statement your lacking of quotation at the end
get rid of the quotation marks on the numbers....
here:

Please Login or Register  to view this content.

BTW you can attach your dummy workbooks so that others may get interested to check your problems.

**MattHamby** · 12-03-2011, 10:41 AM

I tried that and nothing - I don't know what I am doing wrong. I am going to try the vlookup to try to see if that will work. I have uploaded a copy of the file to let some of you experiment with it!

Thanks!

Matt

PS - Hamby, not Humby

**MattHamby** · 12-03-2011, 11:45 AM

Again - I am not getting something here, I have included a new file with my attempt at vlookup - something is just escaping me! I hope that you guys can understand what I am trying to accomplish!

Thanks for any help!

**Andy Pope** · 12-03-2011, 12:02 PM

The formula are fine but the cells G3 and H3 are formatted as text and you have an " infront of the equals sign.

Change cells to general
edit formula remove " and exit cell.

**MattHamby** · 12-04-2011, 12:23 PM

I don't know where the ' in front of the = come from, I couldn't see it so I deleted the = and placed another one, and it works - also, I used the VLOOKUP in lieu of the IF function. I would love it if the drop down list would allow you to see the text in the list and once selected you would get the corresponding number in the cell, but, this works close enough.

Thanks guys for all of your help - I'm off to make another post for another part of this worksheet - I need to hide unused lines until needed - I want the unhide to be automatic.

Matt Hamby