Ask Your Question

Concatenation of a formula

asked 2017-12-16 17:12:20 +0100

RobD gravatar image

I am trying to concatenate an "import" formula so that data from several csv files can be correlated in one master file. I derived the formula by manually copying data from one sheet to the other. I then replaced the specific file reference with a cell reference, A1. A1 in the master sheet contains the name of the required file in cell A1.

The formula I'm using is ;

=CONCATENATE("='file:///home/rob/CSV Files/IG.", A1, ".csv'#$'IG.", A1, "'").F3

But when I run this a 501 error is returned. I'm hoping someone can point out my mistake. Thanks!

edit retag flag offensive close merge delete


About the same question here:
The answer by "MrProgrammer" there should get it clear.

Lupp gravatar imageLupp ( 2017-12-17 13:41:53 +0100 )edit

The answer of MrProgrammer is wrong in one assumption though, the formula expression ='file:///path/name.csv'#$name.A1does import A1 from a temporary import of name.csv (i.e. is a valid external reference), ='file:///path/name.csv'#$name.A2 the second cell and so on.

erAck gravatar imageerAck ( 2017-12-18 17:35:47 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2017-12-17 00:13:12 +0100

erAck gravatar image

updated 2017-12-17 00:14:12 +0100

CONCATENATE returns a string. Pretending that would be "xxx" for simplicity here (instead of the entire "='file:..."), the expression results in "xxx".F3, which is not a valid reference. Assuming the F3 shall be part of the resulting reference it needs to be part of the string and the entire string be indirectly interpreted as reference. If so, then this =INDIRECT(CONCATENATE("'file:///home/rob/CSV Files/IG.", A1, ".csv'#$'IG.", A1, "'.F3")) should do. (untested)

edit flag offensive delete link more


Thanks for that, I'll investigate "Indirect".

However, at this stage I am still not understanding my error. A manual transfer of data pastes the text string ='file:///home .......... into the cell with an appended cell reference which changes with each cell so used. what I was trying to do was to recreate that text string but include a variable element so that the whole master sheet could be filled from a different file by altering the variable. Is that not possible?

Thanks for your help!

RobD gravatar imageRobD ( 2017-12-17 00:43:07 +0100 )edit

Might be possible with using ROW() or COLUMN() expressions and feeding their results as string instead of F3. Experiment.

erAck gravatar imageerAck ( 2017-12-18 17:38:44 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-12-16 17:12:20 +0100

Seen: 489 times

Last updated: Dec 17 '17