Arrays in a cell

  • Thread starter Thread starter Ray Ryan
  • Start date Start date
R

Ray Ryan

I am using a circular function to keep a running total.

A B
1 =a1+b1

I would however like to keep the values that I put into a1 so that I can use
them later. Any suggestions how I can do this?
 
If you entered them in column A of a different sheet (say, Sheet2), you
could use this formula instead of =A1+B1:

=SUM(Sheet2!A:A)

Otherwise you'll need a VBA solution. Perhaps something like this in
your worksheet code module (right-click the worksheet tab and choose
View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Address(False, False) = "A1" Then
If IsNumeric(.Value) Then
Application.EnableEvents = False
Range("B1").Value = Range("B1").Value + .Value
Range("J" & Rows.Count).End(xlUp).Offset(1, 0) = .Value
Application.EnableEvents = True
End If
End If
End With
End Sub

to store the value in column J.
 
Ray,

Consider listing your values, such as down column A. Then use =SUM(A:A) or
=SUM(A2:A50) or something like that to get your sum.
 
Back
Top