Auto Sum unknown number of rows

  • Thread starter Thread starter andy_lakeland
  • Start date Start date
A

andy_lakeland

Im using odbc to import data from a database.

Imported is a list of products in column A and then various columns
containing sales figures.

Because of the import from the database, I do not know how many rows of
products will appear, this week we sell 500 different products, next
week 550.

I need to add totals to the columns directly under the last product.

I've tried creating the totals in an empty template and then inserting
the imported data so the total figures move down and appear at the
bottom. However the imported data
moves the total cells to the right instead of down. Tried all methods
of inserting the data range, but the totals always move sideways.

Does anyone know how to automatically create total cells at the bottom
of a column of values so they appear under the last item ?
Remember,I do not know how many rows will be imported.

Alternatively, does anyone know why my totals move sideways when I
insert data into my template.
I tried selecting rows and columns and everything else I could think of
before inserting the data range.

is VB my only option ?

Thanks
Regards
Andy
 
You can add the formula with a macro. Assuming the data starts in B2,

Cells(65536,2).End(xlUp).Offset(-1,0).FormulaR1C1 = "=SUM(R2C:R[-1]C)"

As for why the data moves to the right when you insert, you have a choice of which direction to
move, down or to the right. Are you sure you are selecting the right option?
 
Hi Myrna,

Thanks for the info, I'll give it a try.

As for the insert, when you get data from an external source you do
not get asked which direction to move cells.
It's not like a normal insert. You choose your cell, then insert data
from external source, and in it goes. There are a few options, but
none seem to work.

Thanks
Andy
 
Hi Andy. See if this does what you want. In the B-column, assuming the
data you are pasting in begins in cell B2, enter the following formulas:

In cell B3: =IF(B2="","",B2)
In cell B4: =IF(B3="","",IF(SUM($B$2:B2)=B3,"",SUM($B$2:B3)))
Then copy the formula in B4 as far down as you need it.

Hope this helps.

Ken
 
Back
Top