how can i make a formula that will expand with the data?
Posted by need advice on July 18, 2001 12:11 PM
i need to make a total profit report for products
each product will appear in a diffrent line
like
product1 3223
product2 343434
----------------
total 665734
the range of data is changing and i dont know how many products it will have.
can i make a formula for one row and make it expand as many rows as needed?
and also how can i design the cells (borders extra..)
and how do i know where to put the total sum line?
Posted by Russell on July 18, 2001 2:46 PM
Say your profit is in column B, like you have below. And say column C is empty. In cell C2 you could type the formula:
=SUM(B:B)
Also, even if you don't have data in every cell, you can have a big "sum area" such as:
=SUM(B2:B65000)
So even if you had data only in rows 2 and 3, it would give you the right sum.
Does this help? Hopefully it will at least give you some ideas....
Regards,
Russell
Posted by Joe Was on July 18, 2001 3:36 PM
This is a VB version, that totals a ever-changing column.
Sub TotalColumn()
By: Joe Was
'Here C1 is the cell you want the total in.
Range("C1").Select
'Here C is the total location and [-2] is the
'data column, -2 is 2 columns to the left of
'your total cell, in this case column A.
ActiveCell.FormulaR1C1 = "=SUM(C[-2])"
'Here D1 is where you want the cursor to end in.
Range("D1").Select
End Sub
Once you ues this code it will place the formula in the total cell. You can modify the code to take a user defined column and total cell, to make it universal. JSW
