Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Data Validation or Conditional Format (or combo of)
I've searched the board this morning without much luck. Hoping someone can
point me in a better direction. Here is what I have. I have a survey of 30 questions. Each question has three parts, which users are asked to rank with a value of 1, 2 or 3. They do this for each of the 30 sets. I have created a directions page for this survey, but as I am sure you have experienced users don't read directions. Some users are adding values other than 1, 2 or 3 and others still are using the same value more than once within a set. For example, INCORRECT ENTRY: A=1, B=1, C=3 CORRECT ENTRY: A=1, B=2, C=3. I have a cell for each of the ranked values for each of the three parts. I can use data validation to prevent users from picking values other than 1, 2, or 3 easily. Where it's getting sticky is trying to come up with a method to keep them from using the same value more than once in the same set. I started to approach this with conditional formats that checked (condition 1)=b2=b4, (condition 2)=b4=b6, (condition 3)=b6=b2. The conditional format displayed an error message I had in a cell next to the answer fields with default text color of white. Some users are still ignoring the error messages... I am guessing there is a better way, that actually prevents the users from making the wrong choices. Any ideas? (thanks in advance) |
#2
|
|||
|
|||
Data Validation or Conditional Format (or combo of)
Hi
Dave Peterson has produced a great survey form, a copy of which can be downloaded from http://www.contextures.com/SurveyForm.zip Perhaps building your survey based on this form would help. Regards Roger Govier Scott wrote: I've searched the board this morning without much luck. Hoping someone can point me in a better direction. Here is what I have. I have a survey of 30 questions. Each question has three parts, which users are asked to rank with a value of 1, 2 or 3. They do this for each of the 30 sets. I have created a directions page for this survey, but as I am sure you have experienced users don't read directions. Some users are adding values other than 1, 2 or 3 and others still are using the same value more than once within a set. For example, INCORRECT ENTRY: A=1, B=1, C=3 CORRECT ENTRY: A=1, B=2, C=3. I have a cell for each of the ranked values for each of the three parts. I can use data validation to prevent users from picking values other than 1, 2, or 3 easily. Where it's getting sticky is trying to come up with a method to keep them from using the same value more than once in the same set. I started to approach this with conditional formats that checked (condition 1)=b2=b4, (condition 2)=b4=b6, (condition 3)=b6=b2. The conditional format displayed an error message I had in a cell next to the answer fields with default text color of white. Some users are still ignoring the error messages... I am guessing there is a better way, that actually prevents the users from making the wrong choices. Any ideas? (thanks in advance) |
#3
|
|||
|
|||
Data Validation or Conditional Format (or combo of)
Thanks for the link Roger, but not sure this helps with the problem I have at
hand. I think this will come in handy the next time I do something with a survey. Any other ideas? Thanks, Scott "Roger Govier" wrote: Hi Dave Peterson has produced a great survey form, a copy of which can be downloaded from http://www.contextures.com/SurveyForm.zip Perhaps building your survey based on this form would help. Regards Roger Govier Scott wrote: I've searched the board this morning without much luck. Hoping someone can point me in a better direction. Here is what I have. I have a survey of 30 questions. Each question has three parts, which users are asked to rank with a value of 1, 2 or 3. They do this for each of the 30 sets. I have created a directions page for this survey, but as I am sure you have experienced users don't read directions. Some users are adding values other than 1, 2 or 3 and others still are using the same value more than once within a set. For example, INCORRECT ENTRY: A=1, B=1, C=3 CORRECT ENTRY: A=1, B=2, C=3. I have a cell for each of the ranked values for each of the three parts. I can use data validation to prevent users from picking values other than 1, 2, or 3 easily. Where it's getting sticky is trying to come up with a method to keep them from using the same value more than once in the same set. I started to approach this with conditional formats that checked (condition 1)=b2=b4, (condition 2)=b4=b6, (condition 3)=b6=b2. The conditional format displayed an error message I had in a cell next to the answer fields with default text color of white. Some users are still ignoring the error messages... I am guessing there is a better way, that actually prevents the users from making the wrong choices. Any ideas? (thanks in advance) |
#4
|
|||
|
|||
Data Validation or Conditional Format (or combo of)
Hi Scott
Try using some And's =AND(A10,A1<4,A1<B1,A1<C1) Something along those lines in Data Validation should sort your problem. Regards Roger Govier Scott wrote: Thanks for the link Roger, but not sure this helps with the problem I have at hand. I think this will come in handy the next time I do something with a survey. Any other ideas? Thanks, Scott "Roger Govier" wrote: Hi Dave Peterson has produced a great survey form, a copy of which can be downloaded from http://www.contextures.com/SurveyForm.zip Perhaps building your survey based on this form would help. Regards Roger Govier Scott wrote: I've searched the board this morning without much luck. Hoping someone can point me in a better direction. Here is what I have. I have a survey of 30 questions. Each question has three parts, which users are asked to rank with a value of 1, 2 or 3. They do this for each of the 30 sets. I have created a directions page for this survey, but as I am sure you have experienced users don't read directions. Some users are adding values other than 1, 2 or 3 and others still are using the same value more than once within a set. For example, INCORRECT ENTRY: A=1, B=1, C=3 CORRECT ENTRY: A=1, B=2, C=3. I have a cell for each of the ranked values for each of the three parts. I can use data validation to prevent users from picking values other than 1, 2, or 3 easily. Where it's getting sticky is trying to come up with a method to keep them from using the same value more than once in the same set. I started to approach this with conditional formats that checked (condition 1)=b2=b4, (condition 2)=b4=b6, (condition 3)=b6=b2. The conditional format displayed an error message I had in a cell next to the answer fields with default text color of white. Some users are still ignoring the error messages... I am guessing there is a better way, that actually prevents the users from making the wrong choices. Any ideas? (thanks in advance) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combo Box or Data Validation | Excel Discussion (Misc queries) | |||
Data Validation | Excel Discussion (Misc queries) | |||
Office2000: Conditional format behaves strangely | Excel Discussion (Misc queries) | |||
Effect of Conditional Formatting, Data Validation | Excel Discussion (Misc queries) | |||
copy conditional format as ACTUAL format | Excel Discussion (Misc queries) |