Creating a List Box in a conditional statement

[yavshalumov]

I am running to a snag when i tried to create a conditional statement and a List Box within a single cell. In the column before I have a List Box that will allow me to select between 5 different values. In the following column i set up a conditional statement so that depending on a specific the item selected in the List Box a number displays.

for Instance:
In C1 List Box has A,B,C,D,X
In D1 I have the following conditional statement: =IF(C1="A",7,IF(C1="B",1,IF(C1="C",5,IF(C1="D",5,""))))

I have a value displayed in D1 if A-D is selected, but if X is selected from C1, I would like to have a drop down appear with values 1 through 5 for the user to select. I was able to get this done by just creating a List box in D1 to have values 1 through 5 and have the if statement in the formula bar. However if I select X and select a value from the drop down and then decide to select A instead I no londer have the IF statement to populate the D1 cell.

any ideas on how I can solve this problem.

Thanks

 

[yavshalumov]

Sorry, I am using Excel 2003 on WinXP