Using TEXT and INDIRECT formulae together

I'm trying to convert a "number" value date to a "text" value date so that I can pull data from multiple spreadsheets (in the same workbook) that are titled with the "text" value date. It appears that the TEXT function does not actually return text, because the INDIRECT formula can't find the spreadsheets in the workbook.

My spreadsheet looks something like this:

Row 1: Date (numerical value)

Row 2: Date (text value supposedly returned by TEXT function from Row 1)

Row 3: Data 1 (pulled from various spreadsheets entitled with Date text values identical to the text in Row 2)
Row 4: Data 2 etc.
Row 5: Data 3 etc.


I know the INDIRECT formula works if I type the date into the cell as text, i.e. with a preceeding apostrophe.

What am I missing?

Thanks in advance for your help.

Thanks for your prompt response ChemistB.

Although your suggestion did not fix the issue, it did lead me to the ultimate source of the problem. Apparently, formatting the date with dashes (minus signs) in it converts it back to a numerical value. Remove the dashes from the formula = everything works okay.