Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is it possible?
Hello,
I wonder if it is possible to make a sheet work in tally chart form? I have satisfaction questionnaires that come to me periodically. I want to know if it is possible that excel can, rather than use '1' as the answer, add this to whatever was in there before? For example: Cell contains I type in Automatically it adds together 3 2 =5 If not any suggestions on the easiest way I can record this information? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is it possible?
see
http://www.mcgimpsey.com/excel/accumulator.html In article , forevertrying wrote: Hello, I wonder if it is possible to make a sheet work in tally chart form? I have satisfaction questionnaires that come to me periodically. I want to know if it is possible that excel can, rather than use '1' as the answer, add this to whatever was in there before? For example: Cell contains I type in Automatically it adds together 3 2 =5 If not any suggestions on the easiest way I can record this information? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is it possible?
Look he
http://www.mcgimpsey.com/excel/accumulator.html -- Kind regards, Niek Otten Microsoft MVP - Excel "forevertrying" wrote in message ... | Hello, | | I wonder if it is possible to make a sheet work in tally chart form? | | I have satisfaction questionnaires that come to me periodically. I want to | know if it is possible that excel can, rather than use '1' as the answer, add | this to whatever was in there before? | | For example: | Cell contains I type in Automatically it adds | together | 3 2 =5 | | If not any suggestions on the easiest way I can record this information? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is it possible?
Thank you both, it works a treat... only one thing.
I'm going to need it to work several times in different areas of a worksheet. Do I need to rewrite the whole code for each bit, or can I just add in at some point through the code? "Niek Otten" wrote: Look he http://www.mcgimpsey.com/excel/accumulator.html -- Kind regards, Niek Otten Microsoft MVP - Excel "forevertrying" wrote in message ... | Hello, | | I wonder if it is possible to make a sheet work in tally chart form? | | I have satisfaction questionnaires that come to me periodically. I want to | know if it is possible that excel can, rather than use '1' as the answer, add | this to whatever was in there before? | | For example: | Cell contains I type in Automatically it adds | together | 3 2 =5 | | If not any suggestions on the easiest way I can record this information? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is it possible?
You didn't say which "whole code" you'd chosen, but in either case,
you'd need to rewrite it a bit to test for the multiple ranges. You can only have one Worksheet_Change() routine in any worksheet code module. In article , forevertrying wrote: I'm going to need it to work several times in different areas of a worksheet. Do I need to rewrite the whole code for each bit, or can I just add in at some point through the code? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is it possible?
I've used:
Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Address(False, False) = "B3" Then If IsNumeric(.Value) Then Application.EnableEvents = False Range("C3").Value = Range("C3").Value + .Value Application.EnableEvents = True End If End If End With End Sub If I can only use 1 Worksheet_Change() do you mean I can only apply this to one other pair of cells? I have around 30 or so that I want it to "tally" up for me. Can you think of any other way I can get this done? "JE McGimpsey" wrote: You didn't say which "whole code" you'd chosen, but in either case, you'd need to rewrite it a bit to test for the multiple ranges. You can only have one Worksheet_Change() routine in any worksheet code module. In article , forevertrying wrote: I'm going to need it to work several times in different areas of a worksheet. Do I need to rewrite the whole code for each bit, or can I just add in at some point through the code? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is it possible?
One way:
Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim vValue As Variant With Target If .Count 1 Then Exit Sub If Not Intersect(.Cells, Range("A1,A4:A7,J10")) Is Nothing Then vValue = .Value If IsNumeric(vValue) Then Application.EnableEvents = False With .Offset(0, 1) .Value = .Value + vValue End With Application.EnableEvents = True End If End If End With End Sub In article , forevertrying wrote: I've used: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Address(False, False) = "B3" Then If IsNumeric(.Value) Then Application.EnableEvents = False Range("C3").Value = Range("C3").Value + .Value Application.EnableEvents = True End If End If End With End Sub If I can only use 1 Worksheet_Change() do you mean I can only apply this to one other pair of cells? I have around 30 or so that I want it to "tally" up for me. Can you think of any other way I can get this done? "JE McGimpsey" wrote: You didn't say which "whole code" you'd chosen, but in either case, you'd need to rewrite it a bit to test for the multiple ranges. You can only have one Worksheet_Change() routine in any worksheet code module. In article , forevertrying wrote: I'm going to need it to work several times in different areas of a worksheet. Do I need to rewrite the whole code for each bit, or can I just add in at some point through the code? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is it possible?
I'm really sorry to be a pain
The cell references you've used. What are they? I mean, if you can explain to me why they are written like that I can sort them out for my worksheet Thanks "JE McGimpsey" wrote: One way: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim vValue As Variant With Target If .Count 1 Then Exit Sub If Not Intersect(.Cells, Range("A1,A4:A7,J10")) Is Nothing Then vValue = .Value If IsNumeric(vValue) Then Application.EnableEvents = False With .Offset(0, 1) .Value = .Value + vValue End With Application.EnableEvents = True End If End If End With End Sub In article , forevertrying wrote: I've used: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Address(False, False) = "B3" Then If IsNumeric(.Value) Then Application.EnableEvents = False Range("C3").Value = Range("C3").Value + .Value Application.EnableEvents = True End If End If End With End Sub If I can only use 1 Worksheet_Change() do you mean I can only apply this to one other pair of cells? I have around 30 or so that I want it to "tally" up for me. Can you think of any other way I can get this done? "JE McGimpsey" wrote: You didn't say which "whole code" you'd chosen, but in either case, you'd need to rewrite it a bit to test for the multiple ranges. You can only have one Worksheet_Change() routine in any worksheet code module. In article , forevertrying wrote: I'm going to need it to work several times in different areas of a worksheet. Do I need to rewrite the whole code for each bit, or can I just add in at some point through the code? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is it possible?
Actually, I figured it out!
Thank you so much for your help! "JE McGimpsey" wrote: One way: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim vValue As Variant With Target If .Count 1 Then Exit Sub If Not Intersect(.Cells, Range("A1,A4:A7,J10")) Is Nothing Then vValue = .Value If IsNumeric(vValue) Then Application.EnableEvents = False With .Offset(0, 1) .Value = .Value + vValue End With Application.EnableEvents = True End If End If End With End Sub In article , forevertrying wrote: I've used: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Address(False, False) = "B3" Then If IsNumeric(.Value) Then Application.EnableEvents = False Range("C3").Value = Range("C3").Value + .Value Application.EnableEvents = True End If End If End With End Sub If I can only use 1 Worksheet_Change() do you mean I can only apply this to one other pair of cells? I have around 30 or so that I want it to "tally" up for me. Can you think of any other way I can get this done? "JE McGimpsey" wrote: You didn't say which "whole code" you'd chosen, but in either case, you'd need to rewrite it a bit to test for the multiple ranges. You can only have one Worksheet_Change() routine in any worksheet code module. In article , forevertrying wrote: I'm going to need it to work several times in different areas of a worksheet. Do I need to rewrite the whole code for each bit, or can I just add in at some point through the code? |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is it possible?
AAAAGGGGGHHHHH!!! ;o)
It just suddenly stopped working. I was trying to protect some of the cells and then when I went back afterwards, it wouldn't work! Any ideas? Also, is it possible to protect the cells that are going to retain the accumulative figure from being deleted? Whatever I did last time clearly wasn't right. "JE McGimpsey" wrote: One way: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim vValue As Variant With Target If .Count 1 Then Exit Sub If Not Intersect(.Cells, Range("A1,A4:A7,J10")) Is Nothing Then vValue = .Value If IsNumeric(vValue) Then Application.EnableEvents = False With .Offset(0, 1) .Value = .Value + vValue End With Application.EnableEvents = True End If End If End With End Sub In article , forevertrying wrote: I've used: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Address(False, False) = "B3" Then If IsNumeric(.Value) Then Application.EnableEvents = False Range("C3").Value = Range("C3").Value + .Value Application.EnableEvents = True End If End If End With End Sub If I can only use 1 Worksheet_Change() do you mean I can only apply this to one other pair of cells? I have around 30 or so that I want it to "tally" up for me. Can you think of any other way I can get this done? "JE McGimpsey" wrote: You didn't say which "whole code" you'd chosen, but in either case, you'd need to rewrite it a bit to test for the multiple ranges. You can only have one Worksheet_Change() routine in any worksheet code module. In article , forevertrying wrote: I'm going to need it to work several times in different areas of a worksheet. Do I need to rewrite the whole code for each bit, or can I just add in at some point through the code? |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is it possible?
The
"A1,A4:A7,J10" refers to cell A1, cells A4 through A7, and cell J10 I just randomly picked them. In article , forevertrying wrote: I'm really sorry to be a pain The cell references you've used. What are they? I mean, if you can explain to me why they are written like that I can sort them out for my worksheet Thanks "JE McGimpsey" wrote: One way: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim vValue As Variant With Target If .Count 1 Then Exit Sub If Not Intersect(.Cells, Range("A1,A4:A7,J10")) Is Nothing Then vValue = .Value If IsNumeric(vValue) Then Application.EnableEvents = False With .Offset(0, 1) .Value = .Value + vValue End With Application.EnableEvents = True End If End If End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|