MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Unchanging array formulas

Posted by Brian on November 10, 2001 6:07 AM

I am trying to find out if it is possible to keep a
cell range in an array formula from changing.

Here is the formula:


I set this up for someone who is a habitual row deleter
and every time a row gets deleted from the CON sheet
it changes this formula's range numbers. (This formula
is on another sheet.)

Is there any way to make the range always point from
2 to 400 whether or not rows are added or deleted?

Any help would be greatly appreciated.


Posted by Aladin Akyurek on November 10, 2001 6:42 AM

Brian --

Change your array formula to:


However, I'd suggest some simplification. You can equally use either the array formula


or this one that does NOT need entering as array formula:




Posted by Brian on November 10, 2001 7:16 AM

Thanks for your help. I was searching for the wrong solutions.
I tried the formula you gave me and it works great, now I can use the
MS help to figure out how it works.

Thanks again.