Hi,

Hope this is the right Community for this question as its not a VBA query. I'm attempting to use Indirect and Rank functions in the same formulae.

Background:

I have a range EW60:GT105 containing values.  Col A60:A105 contains the years 2015 to 2060.

Using a Validation Cell, users can select a year 2015 thru 2060.  Depending on what year is selected I would like the row representing the year to Rank the values in that order.  E:G If 2030 were selected then the Rank formula would be

=RANK(EW123,$EW$123:$GT$123)

="=RANK(EW"&MATCH(EH106,'Number Of Interventions'!$B$60:$B$105,0)+59&",$EW$"&MATCH(EH106,'Number Of Interventions'!$B$60:$B$105,0)+59&":$GT$"&MATCH(EH106,'Number Of Interventions'!$B$60:$B$105,0)+59&")")

The hardcoded 59 is the row number where the Validation range begins.

When I try to put an indirect(formula) I get #Ref!   Would anyone be able to explain why or even better suggest an alternate solution please?

Thanks

John

• Moved by Fei XueMicrosoft contingent staff Monday, February 02, 2015 6:32 AM

I'm not totally sure I follow what you are doing, but based on the location of the quotes, it looks like you are trying to treat the whole formula as indirect, rather than individual range references. Here is how I build out indirect references:

=RANK(EW123,$EW$123:$GT$123)

=RANK(Indirect("EW123"),Indirect("$EW$123:$GT$123") 'convert the ranges to strings by using quotes

=RANK(Indirect("EW" & "123"),Indirect("$EW$" & "123" & ":$GT$" & "123") 'break out the pieces I want to replace

=RANK(Indirect("EW" & MATCH(EH106,'Number Of Interventions'!$B$60:$B$105,0)+59),Indirect("$EW$" & MATCH(EH106,'Number Of Interventions'!$B$60:$B$105,0)+59 & ":$GT$" & MATCH(EH106,'Number Of Interventions'!$B$60:$B$105,0)+59) 

'then just replace those pieces with the calculation of interest

• Proposed as answer by Melon Chen CHNMicrosoft contingent staff, Moderator 5 hours 32 minutes ago
• Marked as answer by Melon Chen CHNMicrosoft contingent staff, Moderator 5 hours 27 minutes ago

Hi John,

Was the Keith's suggestion helpful? Please let us know if you would like further assistance.

Regards,

Hi,

I think Keith's suggestion is correct and I've marked his reply as answer.

Regards,