Summing in a column

  • Thread starter Thread starter Richard
  • Start date Start date
R

Richard

I've got Excell 97. Beginner. Can I make Excel sum up data in a column
without specifying a specific cell to display the result? So, the result
appears in the cell immediately below the last cell in the column containing
data, whatever the position in the column the last cell containing data may
happen to be. TIA. Rich.
 
With a macro you can do this

Exampl;e for column C

Sub test()
Range("C" & Rows.Count).End(xlUp).Offset(2, 0) _
..FormulaR1C1 = "=SUM(R1C:R[-2]C)"
End Sub
 
Ron thanks. When I figure out how to set up a macro later today, I will give
it a shot.

Another thing: The column total as well as displaying in one sheet (call
that sheet1) needs to display in another sheet (call that sheet2). Now I
know normally when the result is displayed in a specific cell in sheet1 you
can do this, but can you have the result go to a cell in sheet2 where the
position of the cell containing the result in sheet1 is dynamic? And, to
make things even more complicated, can you have the result go into a cell in
sheet2 where that cell's location is also dynamic, depending on how much
data happens to be entered in sheet2?

Ron de Bruin said:
With a macro you can do this

Exampl;e for column C

Sub test()
Range("C" & Rows.Count).End(xlUp).Offset(2, 0) _
.FormulaR1C1 = "=SUM(R1C:R[-2]C)"
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


I've got Excell 97. Beginner. Can I make Excel sum up data in a column
without specifying a specific cell to display the result? So, the result
appears in the cell immediately below the last cell in the column containing
data, whatever the position in the column the last cell containing data may
happen to be. TIA. Rich.
Click to expand...
Click to expand...
 
Hi Richard

Maybe this is a option for you
It will give the formula cell a name SumC

You can use in other sheets =SumC then to see the value

Sub test()
With Sheets("Sheet1")
On Error Resume Next
.Names("SumC").Delete
On Error GoTo 0
.Range("C" & Rows.Count).End(xlUp).Offset(2, 0).Name = "SumC"
.Range("C" & Rows.Count).End(xlUp).Offset(2, 0) _
.FormulaR1C1 = "=SUM(R1C:R[-2]C)"
End With
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


Richard said:
Ron thanks. When I figure out how to set up a macro later today, I will give
it a shot.

Another thing: The column total as well as displaying in one sheet (call
that sheet1) needs to display in another sheet (call that sheet2). Now I
know normally when the result is displayed in a specific cell in sheet1 you
can do this, but can you have the result go to a cell in sheet2 where the
position of the cell containing the result in sheet1 is dynamic? And, to
make things even more complicated, can you have the result go into a cell in
sheet2 where that cell's location is also dynamic, depending on how much
data happens to be entered in sheet2?

Ron de Bruin said:
With a macro you can do this

Exampl;e for column C

Sub test()
Range("C" & Rows.Count).End(xlUp).Offset(2, 0) _
.FormulaR1C1 = "=SUM(R1C:R[-2]C)"
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


I've got Excell 97. Beginner. Can I make Excel sum up data in a column
without specifying a specific cell to display the result? So, the result
appears in the cell immediately below the last cell in the column containing
data, whatever the position in the column the last cell containing data may
happen to be. TIA. Rich.
Click to expand...
Click to expand...
Click to expand...
 
When I figure out how to set up a macro later today

See this page

http://www.mvps.org/dmcritchie/excel/getstarted.htm
See David McRitchie's site if you just started with VBA


--
Regards Ron de Bruin
http://www.rondebruin.nl


Richard said:
Ron thanks. When I figure out how to set up a macro later today, I will give
it a shot.

Another thing: The column total as well as displaying in one sheet (call
that sheet1) needs to display in another sheet (call that sheet2). Now I
know normally when the result is displayed in a specific cell in sheet1 you
can do this, but can you have the result go to a cell in sheet2 where the
position of the cell containing the result in sheet1 is dynamic? And, to
make things even more complicated, can you have the result go into a cell in
sheet2 where that cell's location is also dynamic, depending on how much
data happens to be entered in sheet2?

Ron de Bruin said:
With a macro you can do this

Exampl;e for column C

Sub test()
Range("C" & Rows.Count).End(xlUp).Offset(2, 0) _
.FormulaR1C1 = "=SUM(R1C:R[-2]C)"
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


I've got Excell 97. Beginner. Can I make Excel sum up data in a column
without specifying a specific cell to display the result? So, the result
appears in the cell immediately below the last cell in the column containing
data, whatever the position in the column the last cell containing data may
happen to be. TIA. Rich.
Click to expand...
Click to expand...
Click to expand...
 
Richard said:
Ron thanks. When I figure out how to set up a macro later today, I
will give it a shot.

Another thing: The column total as well as displaying in one sheet
(call that sheet1) needs to display in another sheet (call that
sheet2). Now I know normally when the result is displayed in a
specific cell in sheet1 you can do this, but can you have the result
go to a cell in sheet2 where the position of the cell containing the
result in sheet1 is dynamic? And, to make things even more
complicated, can you have the result go into a cell in sheet2 where
that cell's location is also dynamic, depending on how much data
happens to be entered in sheet2?
Click to expand...

What I'm getting at is this:

Sheet1

Column C
x
x
x
X

Result of summation of column C is in cell X , which position is dynamic
depending on how many entries were made in column C. This is possible by
runninga macro kindly written by Ron.

Sheet2

Column D
y
y
y
Y

X
Z

Result of summation of column D is in cell Y , which position is dynamic
depending on how many data entries were made in column D. This is possible
by running a macro kindly written by Ron.

X is the value of summation of column C in sheet1. It's cell position
in sheet2, depends on how many data entries were made in column D.

Z is a value depending on a formula, that uses result Y and result X. It's
cell position depends on how many data entries were put into column D.

Question is, how can you get X and Z in sheet2 to be positionally
dynamic, position depending of course on how many data entries were entered
in column D.

Also remember that X, as a value in sheet2, derives from a cell in sheet1,
which (in sheet1) is positionally dynamic.
 
Back
Top