using RANK function across sheets in XL2k

[Don]

I am trying to use the RANK worksheet function to evaluate
values that exist on multiple spreadsheets.

As far as I can tell, RANK only appears to work for values
that appear on the same sheet.

Whenever I attempt to put in a range that is not on the
same sheet, an error results.

Example :
Sheet1!A1 = 1
Sheet2!A1 = 2
Sheet3!A1 = 3

How would I use RANK to evaluate the above data ?

 

[Harlan Grove]

I am trying to use the RANK worksheet function to evaluate
values that exist on multiple spreadsheets.

As far as I can tell, RANK only appears to work for values
that appear on the same sheet.

Whenever I attempt to put in a range that is not on the
same sheet, an error results.

Example :
Sheet1!A1 = 1
Sheet2!A1 = 2
Sheet3!A1 = 3

How would I use RANK to evaluate the above data ?

Either put all the data on a single worksheet, or use an add-in function or udf
to generate an array from the values in a 3D reference. Laurent Longre's
MOREFUNC.XLL add-in includes a function called THREED which would do this for
you. It's freely downloadable from

http://longre.free.fr/english/