Re : Excel Data>Sort... Tracking of Cell Addresses Used in Formulae

[Ivan Peeler]

1. A formula such as, =Sum(B2:B9),
is entered in Cell-B10.

2. Then, the table (inclusive of other rows and columns) is Sorted.

3. The cells B2:B9 are shifted to other rows respectively.

4. Meaning that, B2:B9 is no longer a contiguous block after the Sort.

5. Correspondingly, the formula, =Sum(B2:B9),
as it is moved to another row away from Cell-B10 through Sort,
does not evaluate well.

6. Please share your experience in the resolution of conflicts as given above.

7. Regards.

 

[GS]

1. A formula such as, =Sum(B2:B9),

is entered in Cell-B10.

2. Then, the table (inclusive of other rows and columns) is Sorted.

3. The cells B2:B9 are shifted to other rows respectively.

4. Meaning that, B2:B9 is no longer a contiguous block after the
Sort.

5. Correspondingly, the formula, =Sum(B2:B9),
as it is moved to another row away from Cell-B10 through Sort,
does not evaluate well.

6. Please share your experience in the resolution of conflicts as
given above.

7. Regards.

The formula auto-adjusts based on position. Normally a 'totals' row or
col would not be included in a sort.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion