How to reference a named data range in a formula using a cell reference ?????

[Mark McInerney]

Hi All,

I have formula as per below - it works fine.

{=LARGE(IF(Departments=B2,Margin),B5)}

I have the word "margin" in cell X1, when I replace it in the formula as follows:

{=LARGE(IF(Departments=B2,X1),B5)}

is does not work...

Any help, greatly appreciated.

Thanks - Mark.

 

[AliGW]

Try INDIRECT(X1) instead.

 

[nardagus]

Hello,

Value of X1 is a pure text for this formula, not a range. You have to force-translate it into named range for a formula.

Try to replace X1 with this:

INDIRECT($X$1,1)

So your formula would look like this:


{=LARGE(IF(Departments=B2,INDIRECT(X1,1)),B5)}

 

[Mark McInerney]

Hi Guys - Thanks for your help on this - worked a treat!!

I Used the indirect function as per AliGW and it was fine - Thanks nardagus for also chipping in - really appreciate it.

I tried to use the indirect function on a command - I have a drop down list with Large and Small as options, in cell A1, any ideas as to how to reference this in the following formula {=IFERROR(LARGE(IF(Departments=$B$4,INDIRECT($D$4)),B7),"")} I tried =IFERROR(indirect(a1)(IF(Departments=$B$4,INDIRECT($D$4)),B7),"") - but did not work out??

 

[AliGW]

Not tested, but try this:

=IFERROR(&"indirect(a1)"&(IF(Departments=$B$4,INDIRECT($D$4)),B7),"")

or

=IFERROR("indirect(a1)"(IF(Departments=$B$4,INDIRECT($D$4)),B7),"")

 

[Mark McInerney]

No joy with either....the entire formula is highlighted when the error message comes up?