Move equations from line to line automatically

[Guest]

I need to create a excel template to import information into a MRP system.
Specificelly for cabinet doors Pricing is done by the square foot so I need
to keep trac of the total price. The problem is the order can be as little as
one door or it can be thousands of doors. I would like to see
HEIGHT WIDTH STYLE QUANTITY SQ FT PRICE

TOTAL
So that the total would always appear below the last door entered. I am
using Office 2003.Is this possible?

 

[Bernie Deitrick]

You can do it either of two ways: the easy way, or the slightly harder way.

The easy way is to move your TOTAL to above the header values, with a formula like
=SUM(H3:H3000)
where column H has your prices, starting in row 3. Then it doesn't matter how many you get.

The slightly harder way is to write a macro to put in the SUM at the bottom: you can incorporate
this into your data import routine, or run it manually. Something like this:

Sub PutSum()
Range("H65536").End(xlUp)(2).Formula = _
"=SUM(H3:H" & Range("H65536").End(xlUp).Row & ")"
End Sub

HTH,
Bernie
MS Excel MVP