Using cell value as worksheet name in formula reference(AVERAGE LARGE IF)

**trizzo** · 01-26-2013, 12:46 PM

Hello,

I have the following formula in the worksheet Sheet1 at Workbook1 :

=(AVERAGE(LARGE(IF((Sheet2!$A$2:$A$114106=$A54),Sheet2!$G$2:$G$114106),{1,2,3,4,5,6,7,8,9,10}))). It is working fine.

I want to replace the "Sheet2 Name" with a value I specified in a given cell, let's say at G4 I wrote "Sheet3" and want to use its value in the above formula. Is it possible to use INDIRECT using the above formula with AVERAGE LARGE and IF.
I tried with INDIRECT function, but it is not working, maybe I am using the wron syntax. If possible please give me an example in the same workbook and in another workbook.

Thanks!

**bebo021999** · 01-26-2013, 01:22 PM

May be try to create an user define name (by Ctrl-F3) for the INDIRECT?

**olasa** · 01-26-2013, 01:43 PM

I haven't tested your formula but this creates an array: =INDIRECT($B$2&"A2:A5").
$B$2 = ''Sheet 1'! (start with two ' characters (not one " character)
//Ola

Attach a sample file if doesn't work.

**Tony Valko** · 01-26-2013, 04:20 PM

Try this array formula**:

=AVERAGE(LARGE(IF(INDIRECT("'"&G4&"'!A2:A114106")=$A54,INDIRECT("'"&G4&"'!G2:G114106")),{1,2,3,4,5,6,7,8,9,10}))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

**trizzo** · 01-26-2013, 05:22 PM

Tony it worked great! Thanks for the other replies really helpfull!!! Now I want it to turn a little bit more complicated, I tried to implement as Tony did I am not sure where I am with errors, if I am missing any parenthesis or not, here is the formula I need to be implemented as Tony did:

=IFERROR(AVERAGE(LARGE(IF((Sheet2!$A$2:$A$114106=$A55)*(Sheet2!$B$2:$B$114106=$B55)*(Sheet2!$E$2:$E$114106=$A$22)*(Sheet2!$I$2:$I$114106=$E$10)*(Sheet2!$F$2:$F$114106=$F$22),Sheet2!$G$2:$G$114106),{1,2,3,4,5,6,7,8,9,10})),""). This formula is working fine.

I just need to insert the INDIRECT on the above formula. I tried many times here, still trying, no success yet. If someone is able to make that I appreciate!

**trizzo** · 01-26-2013, 06:06 PM

I am trying this, but not working(I reduced the formula to find the error, but still not working):

=IFERROR(AVERAGE(LARGE(IF(INDIRECT("'"&G4&"'!$A$2:$A$114106"=$A55)*(INDIRECT("'"&G4&"'!$B$2:$B$114106"=$B55)),INDIRECT("'"&G4&"'!$G$2:$G$114106)),{1,2,3,4,5,6,7,8,9,10})),"")

**trizzo** · 01-26-2013, 07:09 PM

It worked now with the following(a more complex):

=IFERROR(AVERAGE(LARGE(IF(INDIRECT(("'"&G$4&"'!$A$2:$A$114106"))=$A55*(INDIRECT("'"&G$4&"'!$B$2:$B$114106")=$B55)*(INDIRECT("'"&G$4&"'!$E$2:$E$114106")=$A$22)*(INDIRECT("'"&G$4&"'!$I$2:$I$114106")=$E$10)*(INDIRECT("'"&G$4&"'!$F$2:$F$114106")=$F$22), INDIRECT("'"&G$4&"'!$G$2:$G$114106")),{1,2,3,4,5,6,7,8,9,10})),"")

Thanks everyone!!!!!!!!!!

**Tony Valko** · 01-26-2013, 09:23 PM

We can shave a few keystrokes off that like this:

There's no need to use the dollar sign on quoted range references in the INDIRECT function. They will automatically be evaluated as absolute references.

Use a defined name for the LARGE array.
Formulas tab>Defined Names>Define Name
Name: Array
Refers to: ={1,2,3,4,5,6,7,8,9,10}
OK out

Then the formula becomes:

=IFERROR(AVERAGE(LARGE(IF(INDIRECT(("'"&G$4&"'!A2:A114106"))=$A55*(INDIRECT("'"&G$4&"'!B2:B114106")=$B55)*(INDIRECT("'"&G$4&"'!E2:E114106")=$A$22)*(INDIRECT("'"&G$4&"'!I2:I114106")=$E$10)*(INDIRECT("'"&G$4&"'!F2:F114106")=$F$22), INDIRECT("'"&G$4&"'!G2:G114106")),Array)),"")

Still array entered!