Is it possible?

F

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

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

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

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.
 
F

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

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
 
F

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
 
F

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.
 
J

JE McGimpsey

The

"A1,A4:A7,J10"

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



I just randomly picked them.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top