I have a worksheet that I sort one way for data entry and another way for
printing. In the spreadsheet, I have a column that I update every two weeks.
The column has a total; but below it, there is another formula that takes
the total and subtracts the various cells above that are not eligible. When
I go to sort it for printing, the formula's value changes. How do I get the
various cells listed in the formula to change when I sort? i.e. the cell is
X3 when I'm doing my data entry and verifying totals, but when I sort it for
printing, it is now X9 and the formula is still looking at cell X3.
--
Michele

One way would be to Save the file, then Save As to another name. In the Save
As file, highlight everything and copy, then Edit, Paste Special, Values.
This replaces the formulas with the value (not to worry - your formulas are
in the Saved file).
Then you can sort your columns and get the outcome you are looking for.

CaroleO



"Michele" wrote:

I have a worksheet that I sort one way for data entry and another way for
printing. In the spreadsheet, I have a column that I update every two weeks.
The column has a total; but below it, there is another formula that takes
the total and subtracts the various cells above that are not eligible. When
I go to sort it for printing, the formula's value changes. How do I get the
various cells listed in the formula to change when I sort? i.e. the cell is
X3 when I'm doing my data entry and verifying totals, but when I sort it for
printing, it is now X9 and the formula is still looking at cell X3.
--
Michele

Michele,

One way to avoid this problem is to put cells with the formulas at the top
of your sheet, above the area for the data entries, then sort everything
below.

Rob


"Michele" wrote in message
...
I have a worksheet that I sort one way for data entry and another way for
printing. In the spreadsheet, I have a column that I update every two
weeks.
The column has a total; but below it, there is another formula that takes
the total and subtracts the various cells above that are not eligible.
When
I go to sort it for printing, the formula's value changes. How do I get
the
various cells listed in the formula to change when I sort? i.e. the cell
is
X3 when I'm doing my data entry and verifying totals, but when I sort it
for
printing, it is now X9 and the formula is still looking at cell X3.
--
Michele

I don't know how many noneligible cells you have, but another way could be to
subtract those values using a lookup to find them if they have unique entries
in a column that can be used to ID them. Then it won't matter how it's
sorted.
In this example, the identifying info is col U and the number to deduct is
in column X, the fourth column in the lookup table:
=X17-VLOOKUP("ABC",U1:X10,4,FALSE)-VLOOKUP("DEF",U1:X10,4,FALSE)

Data before sorting:
ABC 123-Test 866 93.97
DEF 123-Test 390 111.35
GHI 123-Test 623 57.00
JKL 123-Test 418 65.00
MNO 123-Test 901 142.00
PQR 123-Test 707 260.00
STU 123-Test 426 82.50
VWX 123-Test 581 23.36
YZA 123-Test 777 150.00
BCD 123-Test 319 155.74
Total 1,140.92
Adjusted Total 935.60

And After:
BCD 123-Test 319 155.74
DEF 123-Test 390 111.35
JKL 123-Test 418 65.00
STU 123-Test 426 82.50
VWX 123-Test 581 23.36
GHI 123-Test 623 57.00
PQR 123-Test 707 260.00
YZA 123-Test 777 150.00
ABC 123-Test 866 93.97
MNO 123-Test 901 142.00
Total 1,140.92
Adjusted Total 935.60



"Michele" wrote:

I have a worksheet that I sort one way for data entry and another way for
printing. In the spreadsheet, I have a column that I update every two weeks.
The column has a total; but below it, there is another formula that takes
the total and subtracts the various cells above that are not eligible. When
I go to sort it for printing, the formula's value changes. How do I get the
various cells listed in the formula to change when I sort? i.e. the cell is
X3 when I'm doing my data entry and verifying totals, but when I sort it for
printing, it is now X9 and the formula is still looking at cell X3.
--
Michele