dithering a choice in excel

[mindy monnig]

I have self taught myself many functions in Excel but am struggling with one last task I would like to complete.  I have a worksheet I have created that have four columns to choose from 1, 2, 3 and NA.  Beneath each is a series of rows with different questions.  I want the user to choose only one column for each row then have the others columns for that row become inactive.  So if the user chooses number 3 then the 1, 2 and NA boxes for that row is no longer a choice.  However if the users decides that number 3 is the incorrect choice and wants to change it to a number 2 then the number 2 box is activated and the number 1, 3 and NA boxes are made inactive.  Any help is appreciated.

[#2]

WHy not have just one column with a drop-down in the cell where the user picks 1, 2, 3 or NA? You can use Data Validation for that purpose with the List option.

[#3]

Thanks for the response.  It is because I am counting the number of items chosen in each column then timing by a multiplier and then multiplying by a another number.  I couldn't figure out how to the calculation with a drop down option.  I am certain there is a way I am just not smart enough to figure it out. 

[#4]

Hi, 

Let's assume you have the following "cell addresses" for your first four columns:  A1 for col. 1, B1 for col. 2, C1 for col. 3, and D1 for col. 4 (NA col.). 

Enter the following in cell A1:    AT IF(A1>0, B1 =0,C1=0)

Enter the following in cell B1:    AT IF(B1>0, A1=0,C1=0)

Enter the following in cell C1:    AT IF (C1>0, A1-0, B1=0)

You would, of course, "copy down" the formulas through the rest of the rows.

Do not enter anything in the NA cell.

See if it works. Good luck. l 

[#5]

Have a look at the attached file, perhaps it helps?

[#6]

thanks!