Help Modify Formulas to Work with INDIRECT to Dynamically Reference Worksheets

[Fin Fang Foom]

Hi,

I would like to get the INDIRECT function integrated with these formulas below, so they are reference to worksheet name.


=IFERROR(1/(1/(LARGE(IF('Q1 Year 1'!$B$2:$B$26=Summary!$A$2,CHOOSE(1+(LEFT(A$3,1)="C"),'Q1 Year 1'!$H$2:$H$26,'Q1 Year 1'!$I$2:$I$26)),ROWS($K$1:$K1)))),"")

=IFERROR(INDEX('Q1 Year 1'!$E$2:$E$26,MATCH(I3,'Q1 Year 1'!$D$2:$D$26,0)),"")

=LOOKUP(REPT("z",255),CHOOSE({1,2,3},"",INDEX('Q1 Year 1'!$D$2:$D$26,SMALL(IF('Q1 Year 1'!$H$2:$H$26=K3,ROW($K$2:$K$26)-ROW(K$2)+1),COUNTIF(K$3:K3,K3))),INDEX('Q1 Year 1'!$D$2:$D$26,SMALL(IF('Q1 Year 1'!$I$2:$I$26=K3,ROW($K$2:$K$26)-ROW(K$2)+1),COUNTIF(K$3:K3,K3)))))


Example Below...


=IFERROR(1/(1/(LARGE(IF(INDIRECT("'"&B2&'!"&"$B$2:$B$26")=Summary!$A$2,CHOOSE(1+(LEFT(A$3,1)="C"),INDIRECT("'"&B2&'!"&"$H$2:$H$26"),INDIRECT("'"&B2&'!"&"$I$2:$I$26"))),ROWS($K$1:$K1)))),"")


Any help to get this to work?

[FDibbins]

Hard to say without seeing what you are dealing with, but have you considered using named ranges instead of INDIRECT?

Assuming B2 contains a sheet name, build that into named ranges on that sheet, eg JanProfit or FebExpenses etc, then reference that

[Fin Fang Foom]

I'll try that. And post tomorrow if I can't get it to work.

And yes cell B2 is the worksheet name.


=IFERROR(1/(1/(LARGE(IF(INDIRECT("'"&B2&'!"&"$B$2:$B$26")=Summary!$A$2,CHOOSE(1+(LEFT(A$3,1)="C"),INDIRECT("'"&B2&'!"&"$H$2:$H$26"),INDIRECT("'"&B2&'!"&"$I$2:$I$26"))),ROWS($K$1:$K1)))),"")