Error referencing a cell from of other file with INDIRECT() function

[kebs]

Context: I have large calc (.ods) text-data files, that needs some basic processing (text-processing, sums, sums-if,…), and I want to separate the files holding the data from the file holding the processing.

In the “process” calc file, I enter the considered filename, say in A1 I write “my_file.ods”, and I want to use that as a reference for all the processing.
Now, in another cell, I want to fetch the data from a given cell in that file.

If I do that manually, I end up with a cell holding the following string:

=‘file:///mnt/sda2/some/path/my_file.ods’#$ABC.F2

The cell will now hold the content of cell F2 from sheet ABC from file “my _file.ods”.

Note: I notice that if I enter manually the string

=‘file:my_file.ods’#$ABC.F2

it gets automatically converted to the above string (absolute path), but it does correctly return the cell value.

Problem:
Now I want to use the file name defined in a cell. This sounds like a usecase for the INDIRECT()
function:

Entering in A2 the following function:
=CONCATENATE("‘file:";A1;"’#ABC.F2")

produce the string
‘file:my_file.ods’#$ABC.F2

which is exactly the same as the string above (with the exception of the ‘=’ sign) !

But if I enter in cell A3 the string =INDIRECT(A2), I get an error: #REF!

What did I miss here ?

Edit See also the help website has a page about this.

[Lupp]

Quoting @kebs: “I want to separate the files holding the data from the file holding the processing.”
Why?
This is general-purpose-progamming-and-data-processing-thinking. It’s not the common and well supported way to do similar things with spreadsheet documents. The data keeping and the processing as well are bloated in file size, in RAM usage, and in time expense as compared with the Input-Process-Output concept. Comparing it with using a one-in-all spreadsheet document the difference is less grave, but still relevant. The split-document-concept will cause complications anyway - and you are throwing away the one advantage of using spreadsheets in data processing: the bundling of the data with the processing. Are you aware of the fact that the data you referenced from different documents will actually be present and get stored a second time in/with the referencing document?

Quoting @kebs: “What did I miss here?”
A string passed to INDIRECT() is taken “as-is”. It does not undergo a process of recognition and adaption as a sequence of keystrokes does as soon as you hit ‘Enter’.
=INDIRECT("'" & FullUrlOfTheTargetFile & "'#" & CellAddressIncludingSheet) should do as you expect if the pseudonames are replaced with corresponding string constants or with respective references. At least on Win and with recent versions of LibO it will also work with a full absolute pathname in place of the URL.

[kebs]

Thanks for your answer! Not tested yet, but first, about “Why ?”. Simple: so I can apply the same process to different data files, without having to copy and paste a lot (really a lot…) or columns holding functions. Over the years, this way of working is error-prone and time-consuming. I want to keep the power of spreadsheets processing without having to (again an again) add the processing to the data files I extract from some other information system.

[kebs]

(oh, I can’t even upvote your answer!) And about “Are you aware of the fact that the data […] get stored a second time”. Yes, sure! But it’s actually not to large (say a couple thousand lines at most), and we have sufficient disk storage these days, havent we ?

[kebs]

And thanks for reminding me of the “&” for concatenating. I use to know that but kinda forgot!

[Lupp]

Well, you won’t be surprised too much that you didn’t convince me. Everything beyond my general argument would need a thorough analysis of the specific case. (In fact I also used a few spreadsheet documents specifically made to process other documents. Very special reasons in these cases.)
As far as I understood your explanations I would presumably prefer to use a “process-document” as a template, and import the next set of data into the next instance derived from that template.

[Lupp]

Quoting @kebs: “(oh, I can’t even upvote your answer!)”
Don’t worry. However, you might be allowed to “accept” the answer by clicking on the checkmark.

[kebs]

“I would presumably prefer to use a “process-document” as a template” Yes, that the alternate solution. But it gets cumbersome if the data if spread over several sheets: a lot of manually selecting areas, copy, paste, with risk of errors.

[Lupp]

Quoting @kebs: “But it gets cumbersome if the data if spread over several sheets: a lot of manually selecting areas, copy, paste, with risk of errors.”
Yes, but if the structure of the different data files is too fluctuating to have definite references - whether direct or calculated - in the process file, how would you avoid the problem keeping the data apart from the process?

[kebs]

Good point. Lets say that once the data is correctly formatted in its own file, then with my approach, comparing results from two different data files boils down to changing a file name in a cell. BTW, you are correct in your answer, it works perfectly on a test sheet, but for some reason I can’t get it to work in my (overly complicated ) own sheet! Investigating…

[Lupp]

Quoting @kebs: “I can’t get it to work in my … own sheet.”
I’m not sure if I unsderstand correctly. Do you mean the INDIRECT() formula doesn’t work for you?
If so I’ll try a guess. There are still users giving names to sheets that are not compatible with general naming conventions. They may not start with a letter, contain special characters and spaces and all kinds of flim-flam.
Such bad names must be used enclosed in single quotes to allow the parser to get them as as-if-names.

[kebs]

Thanks, but not the case, regular name. I’m accepting your answer, as it does work, I haven’t had time to search the problem but will let you know.