Maintain the contents of a specific cell, even after a sort

[slam]

I have a spreadsheet of race results. There is a total points column, then adjacent, there is a gap column. The contents of the gap column row by row is as follows:

-
=$X$3-X4
=$X$3-X5

And so on.

How do I exclude this one Gap column from the sort (even though there are columns to be included as part of the sort range at either side), or how do I have them maintain the same cell references after the sort, including the dash (-) in the first row?

Thanks

 

[shg]

You could move it to the left or right of the sort area.

 

[slam]

You could move it to the left or right of the sort area.

It is only relevant to the adjacent column unfortunately, and it would be confusing and irrelevant elsewhere.

 

[slam]

How about this.....

I currently automatically sort the worksheet with this code:


Private Sub Worksheet_Activate()
Range("B3:AC18").Sort Key1:=Range("X3"), Order1:=xlDescending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub


Would it be possible to modify this to also re-apply the dash (in Y3) and the formula in (Y4 through Y18) as follows:


-
=X$3-X4
=X$3-X5
=X$3-X6
=X$3-X7
=X$3-X8
=X$3-X9
=X$3-X10
=X$3-X11
=X$3-X12
=X$3-X13
=X$3-X14
=X$3-X15
=X$3-X16
=X$3-X17
=X$3-X18