Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rank €“ multiple ranges from different worksheets
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rank - multiple ranges from different worksheets
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 "Heather" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rank - multiple ranges from different worksheets
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 -- Biff Microsoft Excel MVP "Heather" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rank €“ multiple ranges from different worksheets
If you're using consecutive sheets you could try something like:
=RANK(AB3,'April 2008:June 2008'!AB$3:AB$358) "Heather" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do i update multiple data ranges across multiple worksheets? | Excel Discussion (Misc queries) | |||
how to merge ranges from multiple worksheets | Excel Worksheet Functions | |||
Combining specific ranges from multiple worksheets into one | Excel Worksheet Functions | |||
copy ranges from multiple worksheets | Excel Worksheet Functions | |||
rank/small with multiple ranges | Excel Discussion (Misc queries) |