I think if you put the corresponding naems on each row, you could do a lot more
with your data.
If you clean up the subtotals (remove those rows), then remove the blank lines,
you can fill the name columns using techniques described at Debra Dalgleish's
site:
http://www.contextures.com/xlDataEntry02.html
Then after you clean up the data, when you insert a new row, you can just copy a
similar one and paste it. Then change the value in column C(?).
But if that's not an option, maybe you could just fix those total formulas so
that the range expands when you add a new row above the total.
For instance:
If the first cell that has a total is in C5 and your formula looks like:
=sum(C1:C4)
you could replace it with this:
=SUM(C1:OFFSET(C5,-1,0))
That offset stuff says to start at the cell that contains the total and move up
one row. So it reacts nicely when you insert a new row 5.
If there are too many to do manually, maybe you could have a macro do it for
you.
But first, try it against a copy of your workbook--or close without saving if it
screws up.
Option Explicit
Sub testme()
Dim myRng As Range
Dim myCell As Range
Dim wks As Worksheet
Dim myFormula As String
Dim colonPos As Long
Set wks = ActiveSheet
With wks
Set myRng = Nothing
On Error Resume Next
Set myRng = .Range("c:c").Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
If myRng Is Nothing Then
MsgBox "No Formulas Found"
Exit Sub
End If
For Each myCell In myRng.Cells
myFormula = myCell.Formula
If LCase(myFormula) Like "=sum(*:*)" Then
'=sum(c1:c4)
'=SUM($C$1:OFFSET(C5,-1,0))
colonPos = InStr(1, myFormula, ":")
myFormula = Left(myFormula, colonPos) & "offset(" & _
myCell.Address(0, 0) & ",-1,0))"
myCell.Formula = myFormula
Else
MsgBox myCell.Address(0, 0) & " was not changed"
End If
Next myCell
End With
End Sub
If looks for formulas like:
=sum(c1:c99) (in cell C100)
and converts it to
=sum(c1
ffset(c100,-1,0))
But there's almost no error checking to make sure it finds the correct formulas,
so double check the output.
If your formula is something different, then don't use this. (or modify it to
do what you want.)
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Short course:
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)
right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side
Paste the code in there. But change the cell addresses to save and the names of
the worksheets.
Now go back to excel.
click on Tools|macro|macros...
click on the macro name (testme)
and then click run.
After you're done, you can dump the macro (or keep it for future use???).
But if you're bothered by any "contains macros" warning message when you open
the file, you can read Debra Dalgleish's site to see how to clean it up.
http://www.contextures.com/xlfaqMac.html#NoMacros
