Sorting in one tabs messes up formulas in another tab

[schlagce]

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

 

[Ron Coderre]

The formulas you're using are referencing the cells....independent of the
values they contain. Sorting is the equivalent of re-typing the values in a
different order, not moving the cells.

It sounds like you want the formulas to adjust their references based on the
location of cell values. Your current structure wouldn't support that, but
this might be an approach you could use:

Example:
A16 contains...

Seq_Name____Amt1___Amt2
1___Delta_____4_____40
2___Charlie___3_____30
3___Bravo_____2_____20
4___Alpha_____1_____10
5___Echo______5_____50

F2: (a seq to reference....eg 4)

This formula "finds" the Seq from F2 in Col_A and
returns the associated average of Cols C and D:
G2:
=AVERAGE(INDEX($C$2:$C$6,MATCH(F2,$A$2:$A$6,0)):INDEX($D$2:$D$6,MATCH(F2,$A$2:$A$6,0)))

If you sort the data in descending order by Seq, that formula will still
find the Seq from F2 in that data and return the same average. In the
example,
G2 returns 5.5 before, and after, the sort.

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[schlagce]

The formulas you're using are referencing the cells....independent of the
values they contain. Sorting is the equivalent of re-typing the values in a
different order, not moving the cells.

It sounds like you want the formulas to adjust their references based on the
location of cell values. Your current structure wouldn't support that, but
this might be an approach you could use:

Example:
A16 contains...

Seq_Name____Amt1___Amt2
1___Delta_____4_____40
2___Charlie___3_____30
3___Bravo_____2_____20
4___Alpha_____1_____10
5___Echo______5_____50

F2: (a seq to reference....eg 4)

This formula "finds" the Seq from F2 in Col_A and
returns the associated average of Cols C and D:
G2:
=AVERAGE(INDEX($C$2:$C$6,MATCH(F2,$A$2:$A$6,0)):INDEX($D$2:$D$6,MATCH(F2,$A­$2:$A$6,0)))

If you sort the data in descending order by Seq, that formula will still
find the Seq from F2 in that data and return the same average. In the
example,
G2 returns 5.5 before, and after, the sort.

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


















- Show quoted text -

Ron,

Yes, thanks - that does do the job. I've worked in Excel for some
time, but have not had the experience of working with the Match and
Index formulas. The only unfortunate part is that it would make the
AVERAGE formulas more lengthy. That formula was just a simpler
example of what I do with some of the numbers. However, it does make
the 2nd tab guaranteed to return the correct value regardless of the
sort order.

Thanks again,
Scotty81

 

[Ron Coderre]

I'm glad you could use that.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

The formulas you're using are referencing the cells....independent of the
values they contain. Sorting is the equivalent of re-typing the values in
a
different order, not moving the cells.

It sounds like you want the formulas to adjust their references based on
the
location of cell values. Your current structure wouldn't support that, but
this might be an approach you could use:

Example:
A16 contains...

Seq_Name____Amt1___Amt2
1___Delta_____4_____40
2___Charlie___3_____30
3___Bravo_____2_____20
4___Alpha_____1_____10
5___Echo______5_____50

F2: (a seq to reference....eg 4)

This formula "finds" the Seq from F2 in Col_A and
returns the associated average of Cols C and D:
G2:
=AVERAGE(INDEX($C$2:$C$6,MATCH(F2,$A$2:$A$6,0)):INDEX($D$2:$D$6,MATCH(F2,$A­$2:$A$6,0)))

If you sort the data in descending order by Seq, that formula will still
find the Seq from F2 in that data and return the same average. In the
example,
G2 returns 5.5 before, and after, the sort.

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


















- Show quoted text -

Ron,

Yes, thanks - that does do the job. I've worked in Excel for some
time, but have not had the experience of working with the Match and
Index formulas. The only unfortunate part is that it would make the
AVERAGE formulas more lengthy. That formula was just a simpler
example of what I do with some of the numbers. However, it does make
the 2nd tab guaranteed to return the correct value regardless of the
sort order.

Thanks again,
Scotty81