When Sorting in Excel

[Rodrigo]

I am using Microsoft XP Professional 2002
and i am using Microsoft Office 2003

I have a spreadsheet with values in column A and formulas in
column B. The cells referred in the sum formula are not consecutive(e.g.
=Sum( A32+A56+A63)). When i sort column A the total in column B changes
because the formula did not follow the values it staid in the cell. Is their
a way to have the formula follow the value when i sort so that the totals
would stay the same?

Thanks
Rodrigo F

 

[JasonP CCTM LV]

I dont think that is possible. The only thing I could think of to do is:

Enter the formulas in column B and verify you have the results you want.

Copy the entire column B, right click on the top of column B and select
"Paste Special". When the next little box opens, click on "Values" in the
"Paste Section" and hit OK.

This will paste over the formulas with the results as actual numbers, but
the formulas will be deleted.

If you want to keep the formulas, but still show the results even after you
sort, then paste special in Column C.

 

[Rodrigo]

That doesnt work, i need to keep the cells with formulas because of auditing
purposes.

Can anyone else help?

 

[Ken Johnson]

That doesnt work, i need to keep the cells with formulas because of auditing
purposes.

Can anyone else help?

Select the column with the formulas.
Go Edit|Replace...
In the Find what: box just type an equals sign.
In the Replace with: box type a character that is neither a wildcard
nor a character that appears in any of the formulas. I used the Pipe
(Shift+ the key below the Backspace key).
Click Replace All.
Now do your sort.
Now use Edit|Replace with the Find what: and Replace with: values
swapped over to return to the formulas.

Ken Johnson

 

[Rodrigo]

I think what you are showing me keeps the fomulas in the cell when you sort.
What i need is for the formula to follow the value or the amount when i sort.
So that when i do sort, the total amount with the formula would not change.

Thanks i appreciated

 

[Ken Johnson]

I think what you are showing me keeps the fomulas in the cell when you sort.
What i need is for the formula to follow the value or the amount when i sort.
So that when i do sort, the total amount with the formula would not change.

Thanks i appreciated

Before you do the sort are you including the formula column in the
selection?
When I did it with the altered formulas (which are no longer formulas)
selected along with column A, the altered formulas moved along with
the moving column A values during the sort. After the sort is done I
use Replace to restore the formulas.
So, if before the sort, A50 had "Fred NURK" and B50 had
=Sum( A32+A56+A63)) and after the sort "Fred NURK" had been sorted up
to A10, then B10 would have the same formula, =Sum( A32+A56+A63)),
because it moved with the pre-sort A50 value.

Ken Johnson

 

[Rodrigo]

Two things. i dont think i mention this, but the cell with the formula cannot
be moved. Just the data in column A. Also, if the data in column A is moved
when is sorted and the formula is reflecting the same cells. Then that feats
the whole purpose that i am referring to. What i need is that if the data in
columns is moved around than the formula changes to to reflect the same
values. But the formula does not move it will stay in the same cell. Example
below.
Before After
| A | B | C | D | E | F | E | G | H
1.| 5 5
2.| 30 8
3.| 54 A2+A5+A7 30 =F3+F5+F6
4.| 8 54
5.| 58 58
6.| 548 70
7.| 70 548

 

[Ken Johnson]

Two things. i dont think i mention this, but the cell with the formula cannot
be moved. Just the data in column A. Also, if the data in column A is moved
when is sorted and the formula is reflecting the same cells. Then that feats
the whole purpose that i am referring to.  What i need is that if the data in
columns is moved around than the formula changes to to reflect the same
values. But the formula does not move it will stay in the same cell.  Example
below.
                      Before                               After
   |  A   |  B  |  C   | D  |  E  |  F  |  E   |  G   |  H      
1.| 5                                            5            
2.| 30                                            8            
3.| 54               A2+A5+A7     30            =F3+F5+F6
4.| 8                                            54            
5.| 58                                            58            
6.| 548                                   70            
7.| 70                                          548            

Before sorting you will need a column of consecutive numbers,
corresponding to the row numbers, next to column A (ie row numbers in
a column inserted between columns A and B).
One way of doing this is to type the row number in the column B cells
next to the first and second column A values, select both those column
B cells, then fill down to be level with the bottom of the column A
values.
Before sorting, select column A and the new column B.
After the sort, the values in column B will act as labels for keeping
track of the values used in the sum formula.
You won't be able to use your original sum formulas, which should now
be in column C, you will need to use a sum formula that is capable of
finding the intended column A values amongst the sorted column A
values.
Using your formula =A2+A5+A7 (originally in B3) as an example, a
suitable sum formula that could use the column B labels to hunt down
the intended column A values could be...

=SUM(SUMIF(B1:B7,{2,5,7},A1:A7))

Notice the 2, 5 and 7 in braces (Shift + the 2 keys to the right of
the P key) correspond to the same numbers in your original formula
that could not keep track of the sorted column A values.
This formula sums the column A values that were in rows 2, 5, and 7
before column A and the new column B were sorted using column A as the
Index column.

Ken Johnson