LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 353
Default Sorting Worksheet and how it effects Formulas with cell references

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
using worksheet references in formulas -sb Excel Discussion (Misc queries) 2 July 27th 06 09:26 PM
Sorting of References for Formulas fak119 Excel Worksheet Functions 1 April 27th 06 05:35 PM
Dynamic formulas including worksheet references lars22222 Excel Worksheet Functions 1 July 26th 05 02:33 PM
Replicating Formulas with Various Worksheet References Cloudburst99 Excel Worksheet Functions 1 January 20th 05 11:15 PM
Replicating Worksheet References in Formulas Cloudburst99 Excel Worksheet Functions 2 January 18th 05 09:27 PM


All times are GMT +1. The time now is 04:41 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"