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 5 hours 21 minutes ago

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

Hi John,

Thanks for posting in MSDN forum.

This forum is for developers discusing developing issues. For example, Excel customatzation or automation. Since the issue is more relative to end-user, I would like to move it to Excel IT pro discussions forum.

The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us.

Thanks for your understanding.

Regards & Fei

Hi,

Oops, sorry for posting in the wrong forum.  Yes, please do move to the XL IT Pro forum. 

Kind regards,

John