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

Ask Your Question
0

Calc- Inserting string variable in call to external file

asked 2021-04-26 00:31:53 +0200

montyj gravatar image

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 flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2021-04-26 15:37:33 +0200

erAck gravatar image

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")
edit flag offensive delete link more

Comments

@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

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

Please, if the answer solves the question click ✔.

m.a.riosv gravatar imagem.a.riosv ( 2021-04-26 19:58:25 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2021-04-26 00:31:53 +0200

Seen: 29 times

Last updated: Apr 26