Referencing Sheet names in formula

[CY078]

Hi All

I am trying write an index formula that references the sheet names.

=INDEX('230701'!$B$6:$Z$30,MATCH(Upload!C2,'230701'!$B$6:$B$30,0),MATCH(I2,'230701'!$B$6:$Z$6,0))

The above formula works ... just a simple index match formula looking up some data in tab '230701'

However I want don't want to manually reference the cell but use a formula. So I tried the below formula .. where A3 is 230701 (General format)

INDEX("'"&A3&"'"!$B$6:$Z$30,MATCH($C3,"'"&A3&"'"!$B$6:$B$30,0),MATCH($I3,"'"&A3&"'"!$B$6:$Z$6,0))

However it does not work ... can anyone shed some light as to what I am doing wrong ??

TIA

 

[Anonymous1378]

The first reference should be INDIRECT("'"&A3&"'!$B$6:$Z$30"). Give the rest a go with the same logic.

 

[CY078]

The first reference should be INDIRECT("'"&A3&"'!$B$6:$Z$30"). Give the rest a go with the same logic.

I tried the below which resulted in a #value! response. Its definitely not the referencing side because it works as a normal index formula.

=INDEX("'"&A3&"'!$B$6:$Z$30",MATCH($C3,"'"&A3&"'!$B$6:$B$30",0),MATCH($I3,"'"&A3&"'!$B$6:$Z$6",0))

=INDEX(("'"&A4&"'!$B$6:$Z$30"),MATCH($C4,"'"&A4&"'!$B$6:$B$30",0),MATCH($I4,"'"&A4&"'!$B$6:$Z$6",0))

=INDEX(("'"&A5&"'!$B$6:$Z$30"),MATCH($C5,"'"&A5&"'!$B$6:$B$30,0"),MATCH($I5,"'"&A5&"'!$B$6:$Z$6,0"))

Thoughts ?

 

[Fluff]

You haven't used the indirect function as Anonymous1378 showed.

 

[CY078]

Sorry my bad ... got to eager and confused myself with indirect & index.

 

[Fluff]

Did you get it to work?

 

[CY078]

Did you get it to work?

I originally used the below formula ... for some reason my workbook is very temperamental with "Indirect" formulas. It can be the same forumla with same lookup points but some work and some don't

=SUMPRODUCT((INDIRECT("'"&A6&"'!$O$2:$BF$2")=Upload!J6)*(INDIRECT("'"&A6&"'!$B$7:$B$42")=Upload!C6)*(INDIRECT("'"&A6&"'!O$7:$BF$42")))

So I was hoping to get an "index" formula without the indirect part.

But it's ok .. there are only 8 tabs ... so I can make a normal "index" formula.

Thanks for checking up