data validation

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,854
i have a sheet with a list of criteria to which I want users to respond with a "1" or a "0" for each one. Is there a better method, or even another method, than using data validation for each cell?
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

babycody

Well-known Member
Joined
Jul 8, 2003
Messages
1,395
Try whole number, between, 0 and 1 in your data validation.
 

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,854
thanks babycody. nice, just right.

bit early in your neck of the woods, isn't it?
 

babycody

Well-known Member
Joined
Jul 8, 2003
Messages
1,395
Your quite welcome. It is actually around 10 o'clock at night here.
 

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,854

ADVERTISEMENT

oops. still haven't learnt. I used to work for a global consulting firm and we would have teleconferences at the strangest of hours. i used to always get the other time zones worng back then also.. DOH!

sleep well when you get there then.

ciao
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,452
Office Version
  1. 365
Platform
  1. Windows
ajm

What about? In Data Validation use Allow: 'List'|Source: 0,1
That way you still give the user the opportunity to type a 0 or 1 in the cell but you give them the additional option of choosing from the drop-down list.

Mind you, neither this nor babycody's suggestion address your question of 'Is there a better or other method than data validation?' I don't think there is a better answer - Data Validation does it simply and well.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
i have a sheet with a list of criteria to which I want users to respond with a "1" or a "0" for each one. Is there a better method, or even another method, than using data validation for each cell?
Hi,
would a little double-click be nice ? or rightclick ?
Code:
Option Explicit

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
'Erik Van Geit
Dim Arr As Variant
Dim k As Integer
Arr = Array(0, 1)

Cancel = True
On Error Resume Next
k = Application.Match(Target.Value, Arr, 0)
On Error GoTo 0
Target.Value = Arr(IIf(k - 1 = UBound(Arr), 0, k))
End Sub
kind regards,
Erik
 

Forum statistics

Threads
1,136,702
Messages
5,677,290
Members
419,684
Latest member
BOB101

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top