Rank – multiple ranges from different worksheets

[Heather]

I would like to perform a rank calculation such as the following:

= RANK(AB3,(AB$3:AB$358,'May 2008'!AB$3:AB$358,'April 2008'!AB$3:AB$358))

But it is returning a #VALUE! error.

Is this because Rank cannot be used to calculate the rank of ranges that are
from different worksheets (e.g. the range in the ‘May 2008’ worksheet and the
‘April 2008’ worksheet)?

If so, can I get around this with another function / combination of
functions? I cannot copy and paste the ranges from the ‘May 2008’ worksheet
and the ‘April 2008’ worksheet into the worksheet where the rank function is
being entered, as this function will be getting copied and pasted over a
300x300 range and will send the size of the workbook into orbit.

 

[Peo Sjoblom]

Being on another sheet has nothing to do with it but using multiple sheets
has everything to do with it. so you could use a third sheet or even another
workbook
where you paste April and May into one array.


--


Regards,


Peo Sjoblom

 

[T. Valko]

Wow! What at coincidence.

Both you and Gary T are looking for formulas that reference the exact same
ranges: 'May 2008'!AB$3:AB$358.

<VBG>

 

[Lori]

If you're using consecutive sheets you could try something like:

=RANK(AB3,'April 2008:June 2008'!AB$3:AB$358)