Make range of cells mutually exclusive

[Diddy]

Hi everyone,

I have a workbook in Excel 2003 that has 20 sheets. The workbook acts as a
questionnaire. Each sheet has 6 columns of statements from which the user has
to choose which one most applies. The statements are marked from 1 to 6. It
does this by using data validation in the row below the statement. So if
there is a statement in A3 worth 6 points then the data validation in A4
allows the user to choose either blank or 6. Down to F3 where F4 allows them
to choose either blank or 1. Then the score for that statement is calculated
by summing A4:F4.

I know it's quite a messy way of doing it but something was needed quickly
and this worked but the job snowballed and there are now up to 16 rows of
statements on each sheet.

Obviously if more than 1 cell in row 4 has a value then the score won't be
right. I was wondering if there was some way to either make the cells in the
range mutually exclusive or to maybe flash up a message to say that more than
one cell has been given a value.

Oh please help me someone!

 

[Dave Peterson]

I'd insert a new column G (say) with a formula like:
=if(counta(A3:F3)<>1,"Please select exactly one!","")

Make the font big, red, bold and maybe it'll help.

 

[Diddy]

Thank you Dave,

I tried to reply earlier and got a message saying website busy so if this is
a repeat -sorry.

Being cheeky now

If I changed the formula so that it returned a number (1) is there a way to
write a macro (associated with a button, Finished Entering?) that would
search the values in G and if there is a number prompt a message box telling
user to check.

I'm not sure that I can get users to scroll across to G to check for
messages - end users are extreme technophobes

Thank you

 

[Dave Peterson]

How about an alternative?

Add some headers (multiple rows maybe???) and freeze the panes so that those
headers are always visible.

Then you can change the formula to include a key word:
=if(counta(A3:F3)<>1,"Error: Please select exactly one!","")

Then in one of those cells in the headers (or anywhere you really want):

=if(countif(g3:G9999,"Error*")>0,"You have errors to clean!","")

Another option would be to apply Data|Filter|autofilter to this range.
Then the user could filter to show all the cells that begin with Error.

Thank you Dave,

I tried to reply earlier and got a message saying website busy so if this is
a repeat -sorry.

Being cheeky now

If I changed the formula so that it returned a number (1) is there a way to
write a macro (associated with a button, Finished Entering?) that would
search the values in G and if there is a number prompt a message box telling
user to check.

I'm not sure that I can get users to scroll across to G to check for
messages - end users are extreme technophobes

Thank you

 

[Diddy]

1st option looks good - KISS Keep It Simple Stupid - the stupid refers to me
of course not you who is Brilliant!

Thank you so much!