Static Cell References in Formula Despite Sorting and Cut/Pasting Range

Originally Posted by longfisher

I have a worksheet with columns of data. On another worksheet which summarizes the data I have formulas referring to the columns of data.

If I delete rows or sort rows on the data sheet the formulas change to reflect those deletions or sorts.

How do I in the formulas refer to a range in the data worksheet such that when the data is sorted or when data rows are deleted the formulas still refer to the same range of cells without changing.

Example:

Original formula:

=COUNTIF('Data'!$T$2:$T$5001,'Data Analysis'!B3)

After deleting the top 10 rows of data:

=COUNTIF('General Delivery'!$T$2:$T$4991,'Data Analysis'!B3)

After then cuttting & then inserting rows 12-22 into row 2

=COUNTIF('General Delivery'!$T$13:$T$4991,'Data Analysis'!B3)

I'd like to find a way such that the delete or cut/insert operations do not change the orginal formula's cell references.

Any help will be much appreciated.

LongFisher

Try a dynamic range
http://www.contextures.com/xlNames01.html#Dynamic

As for sorting, the cell references should stay the same