Search code examples
libreoffice-calc

Create dynamic file reference

I have a spreadsheet in which there are two columns (actually a lot more columns but two for simplicity):

      A        B
1    Name     File
2    AAA      = 'file:///Data/AAA.ods'#$Sheet1.A10
3    BBB      = 'file:///Data/BBB.ods'#$Sheet1.A10
4    CCC      = 'file:///Data/CCC.ods'#$Sheet1.A10

Rather than hardcoding the URL in the second column, I would like to use the value in the Name field (say 'AAA') to create the value in the corresponding File field.

For example, the cell B2 would have a formula in the sense of:

= 'file:///Data/[A2].ods'#$Sheet1.A10

which would give the same result had I hard coded the full URL.

Is there are way to get the URL in the second column to be constructed using the value from the (corresponding) first column?

All I can manage is to get the text value of the URL to appear, but not the actual URL (which then retrieves the external data).

Thanks in advance,

Bernmeister.

Solution

  • You need INDIRECT:

    =INDIRECT("'file:///Data/" & A2 & ".ods'#$Sheet1.A10")