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

Share Improve this question Follow asked Nov 17, 2024 at 1:00 Justin CohenJustin Cohen 711 silver badge22 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 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))