Is it possible?

[forevertrying]

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?

 

[JE McGimpsey]

see

http://www.mcgimpsey.com/excel/accumulator.html

 

[Niek Otten]

Look here:

http://www.mcgimpsey.com/excel/accumulator.html

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| 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?

 

[forevertrying]

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?

 

[JE McGimpsey]

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.

 

[forevertrying]

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]

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

 

[forevertrying]

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

 

[forevertrying]

Actually, I figured it out!

Thank you so much for your help!

 

[forevertrying]

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]

The

"A1,A4:A7,J10"

refers to cell A1, cells A4 through A7, and cell J10



I just randomly picked them.