Calc- Inserting string variable in call to external file

[montyj]

Hola!

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:

='file:///home/user/calc/'Data_Jan-29-2021.csv'#$'Data_Jan-29-2021'.D5

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.

Thanks!

Monty

[erAck]

Note that

='file:///home/user/calc/'Data_Jan-29-2021.csv'#$'Data_Jan-29-2021'.D5

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

='file:///home/user/calc/Data_Jan-29-2021.csv'#$'Data_Jan-29-2021'.D5

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")

[montyj]

@erAck,

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.

Monty

[mariosv]

Please, if the answer solves the question click .