I have a summary tab with column headers for each week with tabs that match the name exactly, but the column headers in the underlying tabs are not consistent.

The current formula manually calls those tab names:

=IFERROR(INDEX('17/09'!$C:$Y, MATCH($B4, '17/09'!$B:$B, 0), MATCH(G$3, '17/09'!$C$3:$Y$3, 0)),0)

I'm trying to update with the INDIRECT function and this formula works:

=INDEX(INDIRECT(CHAR(39)&TEXT(F$2,"DD/MM")&"'!$S:$S"), MATCH($B4, INDIRECT(CHAR(39)&TEXT(F$2,"DD/MM")&"'!$B:$B"), 0),0)

But when I try to patch in the 2nd MATCH it fails:

=INDEX(INDIRECT(CHAR(39)&TEXT(F$2,"DD/MM")&"'!$C:$Y"), MATCH($B4, INDIRECT(CHAR(39)&TEXT(F$2,"DD/MM")&"'!$B:$B"), 0), 
MATCH(F$3, INDIRECT(CHAR(39)&TEXT(F$2,"DD/MM")&"'!$C$3:$W$3", 0)),0)

Error Function INDIRECT parameter 1 value is ''10/09'!$C$3:$W$3'. It is not a valid cell/range reference.

Can you help?

I have a summary tab with column headers for each week with tabs that match the name exactly, but the column headers in the underlying tabs are not consistent.

The current formula manually calls those tab names:

=IFERROR(INDEX('17/09'!$C:$Y, MATCH($B4, '17/09'!$B:$B, 0), MATCH(G$3, '17/09'!$C$3:$Y$3, 0)),0)

I'm trying to update with the INDIRECT function and this formula works:

=INDEX(INDIRECT(CHAR(39)&TEXT(F$2,"DD/MM")&"'!$S:$S"), MATCH($B4, INDIRECT(CHAR(39)&TEXT(F$2,"DD/MM")&"'!$B:$B"), 0),0)

But when I try to patch in the 2nd MATCH it fails:

=INDEX(INDIRECT(CHAR(39)&TEXT(F$2,"DD/MM")&"'!$C:$Y"), MATCH($B4, INDIRECT(CHAR(39)&TEXT(F$2,"DD/MM")&"'!$B:$B"), 0), 
MATCH(F$3, INDIRECT(CHAR(39)&TEXT(F$2,"DD/MM")&"'!$C$3:$W$3", 0)),0)

Error Function INDIRECT parameter 1 value is ''10/09'!$C$3:$W$3'. It is not a valid cell/range reference.

Can you help?

• google-sheets

1 Answer 1

I removed the Excel tag since it's clearly not an Excel question (Excel does not allow the slash character in sheet names).

The issue you're encountering is because the INDIRECT function does not accept cell or range references that are formatted with the CHAR(39) single quotes..

Your formula fails because INDIRECT is not parsing CHAR(39) correctly when combined with TEXT and MATCH in the range string. Instead of using CHAR(39), concatenate the strings directly using single quotes like this:

=INDEX(INDIRECT("'" & TEXT(F$2,"DD/MM") & "'!$C:$Y"), 
       MATCH($B4, INDIRECT("'" & TEXT(F$2,"DD/MM") & "'!$B:$B"), 0), 
       MATCH(F$3, INDIRECT("'" & TEXT(F$2,"DD/MM") & "'!$C$3:$W$3"), 0))