Replicate data from separate files in OpenOffice Calc

Question

I have several .ods files full of data (say data1.ods, data2.ods etc). These files use exactly the same format, so that calculations based on data1.ods would be equally relevant on data from data2.ods. I have another calc file (say main.ods) where I make calculations. In main.ods, I currently have calculations using exclusively cells from data1.ods. I would like now to be able to easily change main.ods so that the data used comes from data2.ods instead, or any other file (calculations remaining the same).

I could do a replace all and replace 'file:///C:/Users/jean__000/Desktop/data1.ods' with 'file:///C:/Users/jean__000/Desktop/data2.ods' when needed, but this feels dirty, and will not work well when I am using data from several files. Instead, I would like to enter the name of the file to be used in a cell, and change this name whenever needed.

For example, cell A1 of Sheet1 in main.ods I could type ="'file:///C:/Users/jean__000/Desktop/data1.ods'#Sheet1", and change the name (or filepath) here when needed. However, I do not manage to use this. If I want to read column B of Sheet1 of data1.ods in column B of Sheet1 in main.ods, I can type in cell B1 =INDIRECT(ADRESSE(1;2;4;1;$A$1)). However, I cannot "drag" that formula to copy the rest of column B.

Note: I am using French version of Calc, hence the "ADRESSE" instead of "ADDRESS".

Answer 1

Set cell A1 to 'file:///C:/Users/jean__000/Desktop/data1.ods'#$Sheet1 without an equals sign or double quotes. In cell B1, enter the formula =INDIRECT($A$1 & "." & ADRESSE(ROW(),COLUMN())).

Now drag and drop to your heart's content. :)

Documentation for ROW() is at https://wiki.openoffice.org/wiki/Documentation/How_Tos/Calc:_ROW_function.