Problem with concatenated formula linking to Share Point site

[TheRobsterUK]

I'm trying to build a formula that reaches out to a separate workbook stored on a Share Point site on my company's Intranet. There are 3 cells with separate "bits" of the formula, which are:

Cell A1 contains the link to the correct folder: 'https://mycompany.sharepoint.com/teams/PSPIs/Shared Documents/General/PSPI Feedback form

Cell A2 contains the workbook name: TestWorkbook.xlsm

Cell A3 contains the cell reference: $B$9

Another cell concatenates all of these (and puts an "=" sign in front of it all) and would ideally return the value from cell $B$9 in the stated workbook. Changing the cell reference, workbook name etc would update the retrieved value in real time.

However it doesn't work. I end up with a string that looks correct:
='https://mycompany.sharepoint.com/teams/PSPIs/Shared Documents/General/PSPI Feedback form/[TestWorkbook.xlsm]Sheet1'!$B$9

However it doesn't actually retrieve any value, it just sits in the cell as though it's just text, as though it's not parsing the text as a formula. Weirdly, if I copy / paste values I end up with:

="="&$A$1&"/"&"["&$A$2&"]"&"Sheet1'"&"!"&$AF$3

Which is the original formula, not values! However if I copy/paste values and BEFORE moving away from the cell with the newly copied formula, I place the cursor in the formula bar at the end of the new formula, then press return, it works! I would hazard a guess that it's not parsing the formula in the right way with the concatenated string, but if you enter things manually and put an "=" sign in front manually etc, it then works.

So I'm asking for advice on how to build the formula and then parse it properly, rather than just have it represented as a string, which is what it seems to be doing.

Regards

[6StringJazzer]

it just sits in the cell as though it's just text

It is just text.

You cannot build a formula by using an "=" then following it with the text that creates a formula. Excel will think it is just a text string.

The INDIRECT function can do this for you:

=INDIRECT($A$1&"/"&"["&$A$2&"]"&"Sheet1'"&"!"&$AF$3)

However, the INDIRECT function will require the target file to be open, or it will give a #REF! error.

You may find it necessary to to this with VBA.