Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Make range of cells mutually exclusive
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! -- Deirdre (totally confused and bemused) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Make range of cells mutually exclusive
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 wrote: 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! -- Deirdre (totally confused and bemused) -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Make range of cells mutually exclusive
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 -- Deirdre "Dave Peterson" wrote: 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 wrote: 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! -- Deirdre (totally confused and bemused) -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Make range of cells mutually exclusive
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. Diddy wrote: 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 -- Deirdre "Dave Peterson" wrote: 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 wrote: 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! -- Deirdre (totally confused and bemused) -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Make range of cells mutually exclusive
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! -- Deirdre "Dave Peterson" wrote: 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. Diddy wrote: 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 -- Deirdre "Dave Peterson" wrote: 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 wrote: 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! -- Deirdre (totally confused and bemused) -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there a way to make 6 columns mutually exclusive? | Excel Worksheet Functions | |||
2 IF Statements not mutually exclusive | Excel Discussion (Misc queries) | |||
Making checkboxes mutually exclusive | Excel Discussion (Misc queries) | |||
Help: Multiple filters with mutually exclusive items | Excel Discussion (Misc queries) | |||
Req: How to create multiple mutually exclusive filters | Excel Worksheet Functions |