Using IF statements linked with combo boxes

**DannyJ** · 08-29-2012, 04:52 AM

Hi,

As you can see I am new to the forum. I have a problem with an excel spreadsheet that I am creating. I am basically trying to create a calculator in excel for a game I used to play. Mainly just to gain excel experience. So far I have the main body of it written but now that I am actually creating the front page of the calculator I have hit a few snags.

I have created two combo boxes, one for race and one for class. Linked them to another worksheet so that when for example Human and Ninja are selected it pulls all the stats for the human ninja onto the page. Here is my example formula. =IF(AND(('Form input fields'!B2=1),('Form input fields'!D2=1)),'Human Ninja'!B2,0)

If I was to replace the '0' at the end with a second IF(AND formula then obviously I could check for every possible combination until it got to one that was correct but with 53 different possibilities that would be a hell of a long formula! Is there an easier or shorter way to write the formula perhaps?

Thanks

Danny

EDIT: Another problem that I am having is that you can only use 7 IF statements in one formula (I think that is correct?) and I require more than that. Again I am hoping that you can perhaps suggest a way to simplify IF statements. The formula that I have at the moment is as follows

Please Login or Register  to view this content.

After the part that sayd B2=9 it says that it is incorrect code. Just to explain what the code is trying to do, Form input fields'!B2 is the target of one of the combo boxes so whatever number is there tells the cell what race to look for. There are 11 different races so obviously 11 different outcomes.

Any suggestions are very much appreciated.

**DannyJ** · 08-29-2012, 08:16 AM

I have noticed that every post I have seen has had a reply today apart from mine. Can I please ask why? Is it because I have not been clear with my problem or just that there isn't a way around this?

Thanks

Danny

**kiran_md** · 08-29-2012, 08:46 AM

Dear Danny,
Try using vlookup function for your all problems.
Example:
=IF(C2=LOOKUP(C2,$E$2:$E$13),VLOOKUP(C2,$E$2:$F$13,2,0),"")
*sorry im new to this forum so i dont know how to attached excel

**DannyJ** · 08-29-2012, 08:51 AM

To attach a spreadsheet you simply click 'Go Advanced' and then there should be a manage attachments button. It is pretty self explanatory after that.

Thank you for the formula as well. It works for the examples I have given you but I have not used LOOKUP before. Will have to go away and read now!

EDIT: I still don't understand what can be done about having too many IF statements in one formula. If that isn't fixed then I can't carry on with the calculator anyway because there are so many bits that need changing.

Regards

Danny