| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Validation morethan once in same Column
I Have a data which Contains 3 columns, Ist Column Contains Date, 2nd Column
Contains Employee Names and IIIrd Column Contains Time... The IInd Column which Contains Employee Names is a Validation List .... Now i want one more validation in the IInd Column, where by the Employee should not be repeated morethan 3 times.. can i apply morethan one validation in IInd column.. any one please help me... |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Validation morethan once in same Column
Hi Kumar:
Say that column C is the column for entering names and the list is stored elsewhe James Smith John Johnson Robert Williams Michael Jones William Brown David Davis Richard Miller Charles Wilson Joseph Moore Thomas Taylor Christopher Anderson Daniel Thomas Paul Jackson Mark White Donald Harris George Martin Kenneth Thompson We want an additional rule that says that a name, say James Smith, can be entered at most three times in the column. Enter this small event macro in the worksheet code area: Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target Set c = Range("C:C") If Intersect(t, c) Is Nothing Then Exit Sub n = Application.WorksheetFunction.CountIf(c, t) If n 3 Then Application.EnableEvents = False t.ClearContents t.Select MsgBox ("You have already used this name three time" & Chr(10) & "pick again") Application.EnableEvents = True End If End Sub This will prevent a name being selected four times. Because it is worksheet code, it is very easy to install and automatic to use: 1. right-click the tab name near the bottom of the Excel window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you have any concerns, first try it on a trial worksheet. If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200821 "Kumar" wrote: I Have a data which Contains 3 columns, Ist Column Contains Date, 2nd Column Contains Employee Names and IIIrd Column Contains Time... The IInd Column which Contains Employee Names is a Validation List .... Now i want one more validation in the IInd Column, where by the Employee should not be repeated morethan 3 times.. can i apply morethan one validation in IInd column.. any one please help me... |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Validation morethan once in same Column
Thank you Gary.. It Worked... but can't we use this without any VBA....It
Helped me a Lot... "Gary''s Student" wrote: Hi Kumar: Say that column C is the column for entering names and the list is stored elsewhe James Smith John Johnson Robert Williams Michael Jones William Brown David Davis Richard Miller Charles Wilson Joseph Moore Thomas Taylor Christopher Anderson Daniel Thomas Paul Jackson Mark White Donald Harris George Martin Kenneth Thompson We want an additional rule that says that a name, say James Smith, can be entered at most three times in the column. Enter this small event macro in the worksheet code area: Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target Set c = Range("C:C") If Intersect(t, c) Is Nothing Then Exit Sub n = Application.WorksheetFunction.CountIf(c, t) If n 3 Then Application.EnableEvents = False t.ClearContents t.Select MsgBox ("You have already used this name three time" & Chr(10) & "pick again") Application.EnableEvents = True End If End Sub This will prevent a name being selected four times. Because it is worksheet code, it is very easy to install and automatic to use: 1. right-click the tab name near the bottom of the Excel window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you have any concerns, first try it on a trial worksheet. If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200821 "Kumar" wrote: I Have a data which Contains 3 columns, Ist Column Contains Date, 2nd Column Contains Employee Names and IIIrd Column Contains Time... The IInd Column which Contains Employee Names is a Validation List .... Now i want one more validation in the IInd Column, where by the Employee should not be repeated morethan 3 times.. can i apply morethan one validation in IInd column.. any one please help me... |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Validation morethan once in same Column
This is a good example of allocating functions. We use Data Validation to
implement the pull-down and an Event Macro to do the count check. -- Gary''s Student - gsnu200821 "Kumar" wrote: Thank you Gary.. It Worked... but can't we use this without any VBA....It Helped me a Lot... "Gary''s Student" wrote: Hi Kumar: Say that column C is the column for entering names and the list is stored elsewhe James Smith John Johnson Robert Williams Michael Jones William Brown David Davis Richard Miller Charles Wilson Joseph Moore Thomas Taylor Christopher Anderson Daniel Thomas Paul Jackson Mark White Donald Harris George Martin Kenneth Thompson We want an additional rule that says that a name, say James Smith, can be entered at most three times in the column. Enter this small event macro in the worksheet code area: Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target Set c = Range("C:C") If Intersect(t, c) Is Nothing Then Exit Sub n = Application.WorksheetFunction.CountIf(c, t) If n 3 Then Application.EnableEvents = False t.ClearContents t.Select MsgBox ("You have already used this name three time" & Chr(10) & "pick again") Application.EnableEvents = True End If End Sub This will prevent a name being selected four times. Because it is worksheet code, it is very easy to install and automatic to use: 1. right-click the tab name near the bottom of the Excel window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you have any concerns, first try it on a trial worksheet. If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200821 "Kumar" wrote: I Have a data which Contains 3 columns, Ist Column Contains Date, 2nd Column Contains Employee Names and IIIrd Column Contains Time... The IInd Column which Contains Employee Names is a Validation List .... Now i want one more validation in the IInd Column, where by the Employee should not be repeated morethan 3 times.. can i apply morethan one validation in IInd column.. any one please help me... |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Validation function with helper column | Excel Worksheet Functions | |||
| Dependant Data Validation - for Whole Column | Excel Worksheet Functions | |||
| VALIDATION FOR A PARTICULAR COLUMN | Excel Discussion (Misc queries) | |||
| How to use condiational validation choices in same column? | Excel Worksheet Functions | |||
| How can I use the same validation for an entire column? | Excel Worksheet Functions | |||
Linear Mode
