Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Scott
 
Posts: n/a
Default 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   Report Post  
Roger Govier
 
Posts: n/a
Default 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   Report Post  
Scott
 
Posts: n/a
Default 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   Report Post  
Roger Govier
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Combo Box or Data Validation FA Excel Discussion (Misc queries) 17 September 27th 05 01:58 PM
Data Validation Mike R Excel Discussion (Misc queries) 11 May 6th 05 02:38 AM
Office2000: Conditional format behaves strangely Arvi Laanemets Excel Discussion (Misc queries) 1 April 7th 05 08:47 AM
Effect of Conditional Formatting, Data Validation Bill Sturdevant Excel Discussion (Misc queries) 1 January 26th 05 12:50 AM
copy conditional format as ACTUAL format Dana Zulager Excel Discussion (Misc queries) 7 December 8th 04 12:02 AM


All times are GMT +1. The time now is 05:49 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"