External file reference determined by formula and cell values

[verus172]

I have reviewed this page on referencing external file references. They provide the following examples:
Linux: =‘file:///home/arindam/price.ods’#$‘fruit price’.B2
Windows: =‘file:///D:/price.ods’#$‘fruit price’.B2

I would like to change the file name based on a formula. If possible, I would also like to change the cell reference based on a formula.

In crude terms: —>

A3=[directory]
A3=/home/arindam/
A4=[filename]
A4=price.ods
A5=[cell reference]
A5="B2"

Cell formula=‘file:/[directory][filename]’$#’‘fruit price’:[cell reference]

square brackets need to insert the value of cells.
I can create the text of the formula above in libreoffice. Can I get LO to calculate the formula generated in a cell (as above) as a formula and display the result.

In essence, I need to change the file name in an external file reference using a cell value.

Any ideas. I am most grateful for your help in advance.

Formatted for clarity @robleyd

[keme]

The INDIRECT() function should do what you need.

Click the function name above to see the online help about the function.

Do note the part about Excel and cell addresses in the help, explaining that verbatim strings are not translated. If your file will be used in apps with different addressing syntax (like LO Calc and MS Excel), make sure you use the ADDRESS() function (or formula constructs for adapting string to acceptable syntax) to build the address string for INDIRECT(). Simple concatenation is likely to fail.