We will be migrating from Ask to Discourse on the first week of August, read the details here

# Calc- Inserting string variable in call to external file

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 edit retag close merge delete ## 1 Answer Sort by » oldest newest most voted 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")

more

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

( 2021-04-26 16:12:52 +0200 )edit