Make Excel use a text string as a formula

[Al_kp]

I've been trying various ways to quickly generate some data I require that involves dynamically linking various workbooks. This question is based on my latest idea.

Is there a way to tell Excel to use text held in a cell as a formula? So for example -

A1 holds the text "='C:\DATA\Results "
B1 holds the text "Jan 19"
C1 holds the text ".xlsx]Sheet 1'!$C$36"

I want the excel to action ='C:\DATA\Results Jan 19.xlsx]Sheet 1'!$C$36 and return the value of that cell. I want this to be automatic with no input from the user.

What I am trying to achieve is more complicated, but this is the core issue; how to make excel action a formula based on text from multiple cells without requiring manual intervention. I know I can use "&" to concatenate the above then paste it as values, but I'd need to do this hundreds of times).

Any help is appreciated.

[#2]

@Al_kp 

Try making a cell where you add up the strings to the final string you want it to be and then use the =INDIRECT function on that cell.

[#3]

@trdebruin Thanks Tim. This works but it requires the referenced workbook to be open. As most instances of this will reference 12 separate books (monthly reporting) this isn't practical.

The best solution I've come up with to date is to save hyperlinks to the separate books to enable them to be quickly opened with just 12 clicks, but it's not a great work around.