Is it possible?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

forevertrying - 08 May 2008 11:42 GMT

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?

Reply to this Message

JE McGimpsey - 08 May 2008 12:02 GMT

see

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

> Hello,
>
[quoted text clipped - 10 lines]
>
> If not any suggestions on the easiest way I can record this information?

Reply to this Message

Niek Otten - 08 May 2008 12:04 GMT

Look here:

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

Signature

Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hello,
|
[quoted text clipped - 10 lines]
|
| If not any suggestions on the easiest way I can record this information?

Reply to this Message

forevertrying - 08 May 2008 12:26 GMT

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?

> Look here:
>
[quoted text clipped - 14 lines]
> |
> | If not any suggestions on the easiest way I can record this information?

Reply to this Message

JE McGimpsey - 08 May 2008 12:51 GMT

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.

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

Reply to this Message

forevertrying - 08 May 2008 13:11 GMT

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?

> 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.
[quoted text clipped - 6 lines]
> > Do I need to rewrite the whole code for each bit, or can I just add in at
> > some point through the code?

Reply to this Message

JE McGimpsey - 08 May 2008 13:22 GMT

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

> I've used:
>
[quoted text clipped - 28 lines]
> > > Do I need to rewrite the whole code for each bit, or can I just add in at
> > > some point through the code?

Reply to this Message

forevertrying - 08 May 2008 13:54 GMT

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

> One way:
>
[quoted text clipped - 47 lines]
> > > > Do I need to rewrite the whole code for each bit, or can I just add in at
> > > > some point through the code?

Reply to this Message

JE McGimpsey - 08 May 2008 22:11 GMT

The

"A1,A4:A7,J10"

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

I just randomly picked them.

> I'm really sorry to be a pain
>
[quoted text clipped - 21 lines]
> > End With
> > End Sub

Reply to this Message

forevertrying - 08 May 2008 14:18 GMT

Actually, I figured it out!

Thank you so much for your help!

> One way:
>
[quoted text clipped - 47 lines]
> > > > Do I need to rewrite the whole code for each bit, or can I just add in at
> > > > some point through the code?

Reply to this Message

forevertrying - 08 May 2008 14:57 GMT

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.

> One way:
>
[quoted text clipped - 47 lines]
> > > > Do I need to rewrite the whole code for each bit, or can I just add in at
> > > > some point through the code?

Reply to this Message