Home |
Search |
Today's Posts |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
using worksheet references in formulas | Excel Discussion (Misc queries) | |||
Sorting of References for Formulas | Excel Worksheet Functions | |||
Dynamic formulas including worksheet references | Excel Worksheet Functions | |||
Replicating Formulas with Various Worksheet References | Excel Worksheet Functions | |||
Replicating Worksheet References in Formulas | Excel Worksheet Functions |