how do I make a sum total to always show up on below the last populated cell in a column

Question

I am making an invoice template that pulls data from an external source. The amount of customer order line rows are variable, so I would like the sum total figure to sum all populated cells in column "D" and to always be below the last customer order line row in column "D" (not in a static cell). please advise. thank you very much

Answer 1

I haven't got a copy of Excel 2003 handy, but have a look at this for Excel 2007, I think it was the same previously.

Basically you need a "header" row, a place for your data then a "blank" row, with your total being from the header row to the blank row. Have the query insert rows / cells as required.

Consider setting a simple query (a database with two fields letter and number) in row 2, with item and amount:

  :   A   :      B      :
=========================
1 :Item   :      £      :
2 :a      :            1:
3 :       :             :
4 :Total  : =SUM(B1:B3) :

Click on the external data then "Properties" and select "Insert whole rows...", (and clear "adjust column width").

Add a few more entries to the database (I did b,2; c,3 ... j,10), refresh the data to get:

  :   A   :      B      :
=========================
1 :Item   :      £      :
2 :a      :            1:
3 :b      :            2:
...
11:j      :           10:
12:Total  : =SUM(B1:B11):

with B12 showing the correct total of 55.