Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
assign occurence number
Hi,
How do you assign an occurrence number in a cell? Lets say Column B (B1:B20). Restriction: 1 is the only valid data to be entered in Column B. Usage possibilities: If the user enters 1 at B3, an occurrence number of 1 appears at C3. If the user enters 1 at B12, an occurrence number of 2 appears at C12. If the user enters 1 at B1, an occurrence number of 3 appears at C1. If the user enters 1 at B20, an occurrence number of 4 appears at C20. And so on¦ Objective: Chronological cell usage indicator. The occurrence number will be used by another sheet. Im still new to excel so please indicate extra details needed for beginners like me. Thank you, Omega |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
assign occurence number
To do chronological counting like you want, you will need VBA. Right-click
your sheet tab, select View Code, and paste this macro into that module. "X" out of the module to return to your sheet. HTH Otto Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If IsEmpty(Target.Value) Then Exit Sub If Not Intersect(Target, Range("B1:B20")) Is Nothing Then Target.Offset(, 1) = _ Application.CountIf(Range("B1:B20"), 1) End If End Sub "omega" wrote in message ... Hi, How do you assign an occurrence number in a cell? Let's say Column B (B1:B20). Restriction: 1 is the only valid data to be entered in Column B. Usage possibilities: If the user enters "1" at B3, an occurrence number of "1" appears at C3. If the user enters "1" at B12, an occurrence number of "2" appears at C12. If the user enters "1" at B1, an occurrence number of "3" appears at C1. If the user enters "1" at B20, an occurrence number of "4" appears at C20. And so on. Objective: Chronological cell usage indicator. The occurrence number will be used by another sheet. I'm still new to excel so please indicate extra details needed for beginners like me. Thank you, Omega |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
assign occurence number
Otto,
It works well. Im amazed! Although, there is another possibility that the user will do that I was not able to mention in my first letter. Given the possibilities mentioned there, the user entered the required data, 1 at B3, B12, B1 and B20. Now the user decided to delete data in B12, then enters 1 in B13, this will result to a duplicate of occurrence # 4. There will be 2 occurrences of 4 now. These are B20 and B13. Is there a way to eliminate duplicates in this task? Please include an optional line just in case I will decide to make the occurrences appear in Sheet2. Thank you very much for your help. Omega ======================= "Otto Moehrbach" wrote: To do chronological counting like you want, you will need VBA. Right-click your sheet tab, select View Code, and paste this macro into that module. "X" out of the module to return to your sheet. HTH Otto Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If IsEmpty(Target.Value) Then Exit Sub If Not Intersect(Target, Range("B1:B20")) Is Nothing Then Target.Offset(, 1) = _ Application.CountIf(Range("B1:B20"), 1) End If End Sub |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
assign occurence number
Think about it for a minute. What do you want Excel to put into that cell
if not another "4"? Write down the logic tree that Excel might be able to use to do what you want. HTH Otto "omega" wrote in message ... Otto, It works well. I'm amazed! Although, there is another possibility that the user will do that I was not able to mention in my first letter. Given the possibilities mentioned there, the user entered the required data, "1" at B3, B12, B1 and B20. Now the user decided to delete data in B12, then enters "1" in B13, this will result to a duplicate of occurrence # 4. There will be 2 occurrences of "4" now. These are B20 and B13. Is there a way to eliminate duplicates in this task? Please include an optional line just in case I will decide to make the occurrences appear in Sheet2. Thank you very much for your help. Omega ======================= "Otto Moehrbach" wrote: To do chronological counting like you want, you will need VBA. Right-click your sheet tab, select View Code, and paste this macro into that module. "X" out of the module to return to your sheet. HTH Otto Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If IsEmpty(Target.Value) Then Exit Sub If Not Intersect(Target, Range("B1:B20")) Is Nothing Then Target.Offset(, 1) = _ Application.CountIf(Range("B1:B20"), 1) End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find the row number of name occurence | Excel Worksheet Functions | |||
Find first occurence of a number in an array 7 cols wide | Excel Worksheet Functions | |||
Finding first occurence of a number | Excel Worksheet Functions | |||
Extract single occurence of order number from list | Excel Worksheet Functions | |||
Assign a number each letter on list compounding occurence @ each e | Excel Worksheet Functions |