Hi,

I'm not sure if this problem was solved before so sorry if this is a
repeat question. Let's say I have one spreadsheet tab that originally
has the following:

Col A Col B
Row 1: 3 3
Row 2: 5 5

In another tab, I have the following formulas that reference the 1st
tab:

Col A
Row 1: =average(A1:B1) - which would yield the number 3
Row 2: = average (A2:B2) - which would yield the number 5

Now let's say I resort all the data in the first tab, by col A, in
descending order. The first tab now looks like:

Col A Col B
Row 1: 5 5
Row 2: 3 3

All is well enough until you look at the values in the cells in the
2nd tab. The formulas stay the same, but this time they yield 5 for
cell A1 and 3 for cell A2. This is not the same values that those
cells originally had.

I had assumed that if I had resorted the cells in one tab, that the
formula in the other tab would know what rows to look at when the data
got resorted. But, I guess this is not the case. I had tried to avert
this problem by using absolute addresses in the formula, but this did
not help.

Does anyone know a way to code the formulas in the 2nd tab, such that
they reference the correct rows in the 1st tab? While I have presented
a simple example, the real world example is hundreds of rows long.

Although I could protect the 1st tab to prevent sorting, I do have a
periodic need to resort. The 1st file contains a list of items and
occassionally, I need to resort in alpha order.

Any help would be greatly appreciated.

Thanks,
Scotty81