Restricting entry into multiple cells

[maryfay]

I have attached a sample Excel file, in which I have 4 cells that each have a drop-down list from 1 through to 5 to select from.
There is a simple autosum for the total.

Any one of the four cells can have a value from 1 to 5, but the other three MUST REMAIN ZERO, as in the sample from my spreadsheet below.
How do I ensure that only one choice can be made and the maxium total is 5. I tried Data Validation on the subtotal but this
does not work on formulas, I can use Conditional Formatting, but just a change of colour does not really alert the user that
they have entered more than 1 score. Is there any way an ALERT MESSAGE can pop up to warn them that they can only have 1 score.
-------------------------------------------------------------------------------------------------
Each Score has a drop-down choice from 1 to 5.
You can only select a single score, for example
in the following table, a 4 has been selected for Score 3
therefore, you cannot select anything for Scores 1, 2 or 4

Score 1
0
Score 2
0
Score 3
4
Score 4
0
Total
4

---------------------------------------------------------------------------------------
I thank you for any help that I can get on this problem.

Mary

[benishiryo]

hi Mary, welcome to the forum. 1 way is to create 2 Named Range.
Score:
=Sheet1!$I$5:$I$10
List:
=IF(SUM(Sheet1!$A$9:$D$9)=0,Score,0)

you can then use the List inside the Data Validation List. to create the Named Range, press CTRL + F3. Click on "New" & fill in the name. in "Refers to", fill in my formula. for the instructions, it's under the "Input Message tab" when you are inside the Data Validation window.

[Jakobshavn]

Insert the following event macro in the worksheet code area:

Please Login or Register  to view this content.

Once a selection has been made in one of the cells, the other cells are automatically set to 0.


Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!


I have attached an example.

[alansidman]

This might be helpful

http://uk.answers.yahoo.com/question...0105009AAExr5n

[maryfay]

Hi, I thank you for this code, as you suggested, I made a trial sheet and I ran it and it is fantastic, I can only have a single cell with a value and the other 3 go back to 0, which is exactly what I need. I hate to bother you further, but I have 10 rows of these scores, exactly like the one you got to work, with a row of text
separating each one. Can your code be applied to each of these rows independently, as I modified the code to include the entire range, ie. $A$9:D28 but it removed
my text and I was only allowed to have a single value within the whole range. I did not know how to attach my Excel file, but below is a copy of it, starting in Cell A8 for the heading and keeping A9 as the first drop-down value.

Score 1 Score 2 Score 3 Score 4 Total
0 0 0 5 5
Score 1 Score 2 Score 3 Score 4 Total
0 2 0 0 2
Score 1 Score 2 Score 3 Score 4 Total
0 0 3 0 3
Score 1 Score 2 Score 3 Score 4 Total
0 1 0 0 1
Score 1 Score 2 Score 3 Score 4 Total
0 0 0 5 5
Score 1 Score 2 Score 3 Score 4 Total
0 3 0 0 3
Score 1 Score 2 Score 3 Score 4 Total
0 0 0 5 5
Score 1 Score 2 Score 3 Score 4 Total
0 1 0 0 1
Score 1 Score 2 Score 3 Score 4 Total
0 0 0 5 5
Score 1 Score 2 Score 3 Score 4 Total
0 0 2 0 2


I hope I am making sense, thank you again. Mary

[Jakobshavn]

Here is an updated version.

[maryfay]

Hi, You ranging naming and data consolidation has allowed me to create multiple rows of independent scoring, so I would like to say thank you very much this has been of great help. Mary

[maryfay]

I would like to thank you again for your time and help. I am a real novice when it comes to VBA code, I need to study it and learn more about code and how it relates to the data. I am glad I found this forum as I believe it will help me lots. Mary

[Jakobshavn]

Thank you for the feedback!