You are truly an MVP with me too. This worked fine and I have been able to
make mods to my situation (change the range, search for a cell value instead
of text). I appreciate your looking at the problem and the proposed solution.

"T. Valko" wrote:

This is a real "mouthful" but it works (without having to use volatile
functions!).

Assume the Grand Total can be in either H5:J5

The formula is entered in cell L6.

=RANK(INDEX(H$6:J$500,ROWS(L$6:L6),MATCH("Grand
Total",H$5:J$5,0)),INDEX(H$6:J$500,,MATCH("Grand
Total",H$5:J$5,0)))+COUNTIF(INDEX(H$6:J$500,ROW(L$ 6:L$6),MATCH("Grand
Total",H$5:J$5,0)):INDEX(H$6:J$500,ROWS(L$6:L6),MA TCH("Grand
Total",H$5:J$5,0)),INDEX(H$6:J$500,ROWS(L$6:L6),MA TCH("Grand
Total",H$5:J$5,0)))-1

Copied down

--
Biff
Microsoft Excel MVP


"Bigfoot17" wrote in message
...
I am using a Pivot Table and I have some formulas to rank the top numbers
in
each column. My problem is - at times there will be 7 columns and other
times 3 or 4, so the "Grand Totals" column moves.

Here is my formula:RANK(J6,$J$6:$J$500,0)+COUNTIF(J6:$J$6,J6)-1
But the the reference to J may need to be changed.

Basically: If J5 ="Grand Total" use the above formula, but if i5="Grand
Total" then use RANK(i6,$i$6:$i$500,0)+COUNTIF(i6:$i$6,i6)-1