Solved: Dynamic named range

[QuickDraw]

I can't seem to get this to work. I want to name a range from AK2:AV20000 as "myRange"). AV is the last column (for now). Rows 20002 and 20004 contain summation formulas.

1. How can I set a dynamic range so the range name remains if rows/cols are deleted/added?
2. If Sheet4 containg the range is labelled "myData", can I use Sheet4 of do I have to use the name ofthe sheet?

Thanks

[matthewspatrick]

I can't seem to get this to work. I want to name a range from AK2:AV20000 as "myRange"). AV is the last column (for now). Rows 20002 and 20004 contain summation formulas.

1. How can I set a dynamic range so the range name remains if rows/cols are deleted/added?
2. If Sheet4 containg the range is labelled "myData", can I use Sheet4 of do I have to use the name ofthe sheet?

Thanks

Assuming the following:


You will always start in Col AK, and you want all columns used from AK:IV to be part of the range
You have headings in Row 1, and the 'real' range has to start in Row 2. Further, you have no blank rows interspersed in the data, and you always have two summary rows that should be excluded

then try using this formula for the range:

=OFFSET('The sheet'!$AK$2,0,0,COUNTA('The Sheet'!$AK:$AK)-3,COUNTA('The Sheet'!$AK$1:$IV$1))

[QuickDraw]

Many thanks matthewspatrick :thumb