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...

I suppose you mean

=RANK(A2,K:K,0)

When you insert columns, the formula will be changed automatically

To avoid looking at the total, don't use K:K, but use K1:K100 (or whatever
your last row is) instead

--
Kind regards,

Niek Otten

"flow23" wrote in message
...
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...

but then rows are added regularly aswell as the columns

and the formula should say a2 = Rank (K2,K:K,0)

"Niek Otten" wrote:

I suppose you mean

=RANK(A2,K:K,0)

When you insert columns, the formula will be changed automatically

To avoid looking at the total, don't use K:K, but use K1:K100 (or whatever
your last row is) instead

--
Kind regards,

Niek Otten

"flow23" wrote in message
...
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...

Still shouldn't make any difference as Niek says.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"flow23" wrote in message
...
but then rows are added regularly aswell as the columns

and the formula should say a2 = Rank (K2,K:K,0)

"Niek Otten" wrote:

I suppose you mean

=RANK(A2,K:K,0)

When you insert columns, the formula will be changed automatically

To avoid looking at the total, don't use K:K, but use K1:K100 (or
whatever
your last row is) instead

--
Kind regards,

Niek Otten

"flow23" wrote in message
...
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...

but how would I know how many rows are there as they keep varying....
K1: K100 therefore cant be done


"Bob Phillips" wrote:

Still shouldn't make any difference as Niek says.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"flow23" wrote in message
...
but then rows are added regularly aswell as the columns

and the formula should say a2 = Rank (K2,K:K,0)

"Niek Otten" wrote:

I suppose you mean

=RANK(A2,K:K,0)

When you insert columns, the formula will be changed automatically

To avoid looking at the total, don't use K:K, but use K1:K100 (or
whatever
your last row is) instead

--
Kind regards,

Niek Otten

"flow23" wrote in message
...
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...

OK, leave it as K:K. Use Rank to find the second largest item

--
Kind regards,

Niek Otten

"flow23" wrote in message
...
but how would I know how many rows are there as they keep varying....
K1: K100 therefore cant be done


"Bob Phillips" wrote:

Still shouldn't make any difference as Niek says.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"flow23" wrote in message
...
but then rows are added regularly aswell as the columns

and the formula should say a2 = Rank (K2,K:K,0)

"Niek Otten" wrote:

I suppose you mean

=RANK(A2,K:K,0)

When you insert columns, the formula will be changed automatically

To avoid looking at the total, don't use K:K, but use K1:K100 (or
whatever
your last row is) instead

--
Kind regards,

Niek Otten

"flow23" wrote in message
...
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...