Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
    
I've been frustrated by this before. My solution was to put the summary columns at the top of the spreadsheet and have the sum include the whole column. –  yhw42 Sep 13 '10 at 21:26

1 Answer 1

up vote 0 down vote accepted

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.

share|improve this answer
    
Please give me the exact forumula to copy and paste for column D. I also need a prefix saying "total amount due:" –  Nathaniel_613 Sep 13 '10 at 21:33
    
BTW ,I am using 2007 and saving it as 2003 –  Nathaniel_613 Sep 13 '10 at 21:35
    
Added a bit more detail. –  Neal Sep 13 '10 at 22:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.