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)

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)

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)

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)