Sorting Changes Calculated Value

[Defend30]

I've got a spreadsheat listing expenses in a column as well as payee, category, subcategory, and date. I'm summing the total expenses at the bottom of the expenses column through a certain range. When I sort all of these columns either with pull down menus or a VBA command button, the calculated value will change depending on how the table is sorted. Can anyone explain what is happening? Thank you.

 

[izzy]

I'm summing the total expenses at the bottom of the expenses column through a certain range.

Example: Your formula is =sum(D3:D8)
When you sorted your data the numbers in range D3:D8 changed. You might be able to change your sum formula to be dynamic using sumif, sumproduct or others.

 

[Defend30]

Thanks for your reply. I still don't quite understand. If I'm summing over, as you say, D3:D8, and there are no other values anywhere else on the sheet, then if I sort that same range based on some other condition, why would the total be any different? I'm still summing the same values, just in a different order. No new or different values should show up in that range.

 

[Defend30]

Silly me. A little brain power might have helped. A handful of entries contained some relative references (i.e. =20.00+d2). Obviously, when you sort, this can screw everthing up. Problem solved, ego spanked.

That brings up another question, however. What is required to sort when relative references are involved, so as to maintain the desired relationship?

Thanks again.

 

[izzy]

If you always want to add to cell D2 then change your formula to this
=20.00+$D$2
Using dollar sign keeps the cell reference static. So no matter where the formula gets moved to with a sort or with copy/past D2 will be referenced.

You can mix this up as well by putting the dollar sign infront of either the column or row to maintain either one as a static reference.
Examples
=A5+D$2 Row 2 is static
=A$15+$D2 Row 15 is static and D Column is static

 

[Defend30]

Not quite what I had in mind. Say I have three values input in a table, cell D2 is 10, cell D3 is 20, and cell D4 is =25-D3. If I sort these by value in ascending order, cell D2 becomes =25-D1 (which calculates to 25 assuming D1 has no value), cell D3 becomes 10, and cell D4 is 20. I want to know how to input my data such that even after sorting the correct value is calculated (i.e., the original =25-D3 will dynamically change after the sort, so I'm always subtracting the 20 from 25) Thus the table sorted would be D2 is 5, D3 is 10, D4 is 20. Thank you again for your time, Izzy.

 

[izzy]

I think you would need to reference whatever it is you are trying to subtract in the column next to (or any corresponding) the column to are doing your math in. Here's a little example.

Book1
	A	B	C	D
1	Original	Name	Number
2		Input1	10
3		Input2	20
4		Result	5
5
6	Sorted	Name	Number
7		Result	#VALUE!
8		Input1	10
9		Input2	20
10
11	Corrected	Name	Number
12		Result	5
13		Input1	10
14		Input2	20
Sheet1