Calc- Inserting string variable in call to external file


Need some string variable help!

I have column A that has literal text data in the format Jan-01-2021.

I have column B that points to an external .csv data file and a specific cell.

Manually entered, this entry into the B cell returns the proper data:


However, I need to use the contents in the corresponding A cells as the date variable in the file name in column B cells.

In this example I need the two “Jan-29-2021” strings in the B1 cell text to use the text contents of A1.

I have tried various combinations of (") and (’) and (&) joiners, but cannot get the right combination.

Most of the time I get the “#NAME?” error but have seen others as well.



Note that


has an extraneous ' apostrophe between path and file name and instead should read


To form references of strings use the INDIRECT() function, just like in-document references, so

=INDIRECT("'file:///home/user/calc/Data_" & A1 & ".csv'#$'Data_" & A1 & "'.D5")


Thank you! That did it! I had only briefly tried INDIRECT but of course had the same issues with the use of the ("), (’), and (&) chars.

As to the extraneous ’ char, that was not in my original content. It seems to have been the use fo the “preformatted text” function here on the forum. Next time I will just use the “” function to input code I guess.

Again, thanks for the solution…I will be using this a lot.


Please, if the answer solves the question click :heavy_check_mark:.