Cell A2 needs to rank

formula looks like this =RANK(K2,K:K,0)
Range K:K is defined as "Total"

the problem is every month, a new column is added in between (months) this
pushes total to next column ( say L, M , N)

thus the formula wont work in a months time..

also when the formula is copied down.. using a macro.. The last row also
contains Grand Total. Thus that row is RANK 1... (because its total).

Is there a macro to avoid this.?



Its copies down...