yogi_Set Up Formula So That Deleting the First And Or The Last Row Or Column Of A Range It Continues To Work

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #622   Jul 13, 2012     www.energyefficientbuild.com.


user blackburied said:
Deleting a row in one sheet causes a formula in another sheet to change!
I have a spreadsheet/form with two sheets.  The first is named "History".  On the second sheet, I have the array formula equation in A2 (row one is headers, the same as in the first sheet):

=sort(filter(History!A2:I,not(History!H2:H)),7,TRUE,1,TRUE)

If I go to the first sheet, named "History", and delete row 2, the equation in the second sheet becomes:

=sort(filter(History!A2:I,not({}:{}:C[7])),7,TRUE,1,TRUE)

... and is an error.

Why would my array formula get changed just because I delete the root cell of that formula?  The formula should remain constant on that sheet, no matter what I do to the other sheet?  What am I missing here?  How do I make that equation stay constant, even if the some of the data it's using changes (or gets deleted)?

-------------------------------------------------

if the beginning row/column or the ending row/column of the specified range is deleted, the reference to cells of interest is lost ... workaround is to delineate the cells of interest using the INDIRECT function as shown in the following