Concatenation of a formula

[RobD]

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!

[Lupp]

About the same question here: Concatenation of a formula (View topic) • Apache OpenOffice Community Forum
The answer by “MrProgrammer” there should get it clear.

[erAck]

The answer of MrProgrammer is wrong in one assumption though, the formula expression ='file:///path/name.csv'#$name.A1 does 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]

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)

[RobD]

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!

[erAck]

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