Increment Sheet reference automatically in formula?

[slam]

Hi gang!

I have the following VLOOKUP in C2:

=VLOOKUP($A2,'2020-03-18'!$A$2:$C$100,3,FALSE)

I want to convert the sheet name part of this formula, '2020-03-18'! to be a cell reference, specifically C1.

I have all my sheet names listed in row 1, column C and onward, using the instruction found here which utilizes name manager and this formula:

=INDEX(SheetNames,Sheetnumbers!C1)

In my VLOOKUP, is there a way that I can replace the sheet name so I can copy the formula down and across to have it automatically update with the sheet name that's listed in row 1?

Thanks!

 

[slam]

I've evolved my formula a little, and I'm using INDIRECT now, but it's giving me a #REF!

Anything obviously wrong with it?

=VLOOKUP($A2,INDIRECT(C$1&"!$A$2:$C$100"),3,FALSE)

Thanks!

 

[Fluff]

Try it like

=VLOOKUP($A2,INDIRECT("'"&C$1&"'!$A$2:$C$100"),3,FALSE)

 

[slam]

Perfect, thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback.