I realize Excel is not the best tool for this but this is where I need to
start. I have a multi-worksheet file. One worksheet has raw data, over 700
rows, 20 columns. The other worksheets have a variety of complex formulas,
conditionals, and arrays. Over time I will be adding more rows. Is the best
way to handle this without changing every formula to name each range and
reset the range when I add rows.

Thanks.

hi
the trick is to reset the range automaticly using an event like the before
save event.
this macro will reset the range each time you save the file. it will
remember where you are in the file and return to the place you were at after
the macro has run wheather you are adding rows or doing something else. you
may notice a quick "blip" as the macro runs. you will have to adjust the
macro to fit your data ie your sheet name and your range name.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim rng As Range
Dim sht As Worksheet
dim srng as range

Set rng = ActiveCell
Set sht = ActiveSheet

Sheets("yourSheetName").Select
Range("A1").Select
set srng =Range(Range("A1"), _
Range("A1").Offset(0, 1) _
.End(xlDown).Offset(0, 20))
ActiveWorkbook.Names.Add Name:="YourRangeName", _
RefersToR1C1:= srng

sht.Select
rng.Select

End Sub

if you have multiple sheet and multiple ranges then add to the above macro
to accomodate the additional sheets and ranges.

Regards
FSt1

"PAL" wrote:

I realize Excel is not the best tool for this but this is where I need to
start. I have a multi-worksheet file. One worksheet has raw data, over 700
rows, 20 columns. The other worksheets have a variety of complex formulas,
conditionals, and arrays. Over time I will be adding more rows. Is the best
way to handle this without changing every formula to name each range and
reset the range when I add rows.

Thanks.

Name the ranges and make them dynamic ranges.

See Debra Dalgeish's site for naming dynamic ranges.

http://www.contextures.on.ca/xlNames01.html#Dynamic


Gord Dibben MS Excel MVP

On Wed, 14 Nov 2007 13:32:01 -0800, PAL wrote:

I realize Excel is not the best tool for this but this is where I need to
start. I have a multi-worksheet file. One worksheet has raw data, over 700
rows, 20 columns. The other worksheets have a variety of complex formulas,
conditionals, and arrays. Over time I will be adding more rows. Is the best
way to handle this without changing every formula to name each range and
reset the range when I add rows.

Thanks.