Link between Calc sheets in separate files and use it as database in Base

[jagerlime]

After endless hours of tryring to find a solution for my problem I need some help.

I have three files:

1. Counters.ods (calc)
2. Evidence.ods (calc)
3. Raports.odb (base)

Each .ods file has the same amounts of sheets, with the same name corresponding to years: ex. 2018, 2019, 2020, etc. Data between this two files are linked via INDIRECT().

Ex. in cell H2 Evidence.ods I used =INDIRECT("‘file:Counters.ods’#$’"&$J2&"’."&ADDRESS(ROW();2;4)) where J2 correspond to variable of a sheet name.

In Calc .ods files all works fine, but when I go to Base Raports.odb where database is linked to Evidence.ods all cells where I used INDIRECT formula to link data between files contains value: 0

I know that I can link data with simple ex. =‘Counters.ods’#$‘2020’.C2 but it will wont work when someone will create a new sheets and Copy&Paste all the structure.

Is there any solution for that?

[Ratslinger]

Hello,

I am certainly not proficient in Calc but took a look at this because of Base.

Either I am missing something here or this is a bug. I am answering this because I can get it to work with a caveat. Someone else may see my response and have something else to add. If not you should report it as a bug here → Bugzilla.

Spent most of the time learning the use of INDIRECT and got that working. Finally got the Base file up and the results were the same as you specified. Went one step further and in the same column as the INDIRECT column I add your ‘simple’ statement:

='Counters.ods'#$'2020'.C2

to see what the results would be in Base. To my surprise, ALL cells/columns with INDIRECT now worked in Base.

Went another step and moved your ‘simple’ statement to a column without INDIRECT. Again it worked. And one more step, moved it to a sheet by itself and it still worked.

That’s what I have uncovered. Either someone else may have something to add or this is a bug.

Hope this is of some help.

Edit:

Turns out started having problems when using a different sheet (your J2). Worked with one sheet then changed and did not work. So I had the ‘simple’ statement on a separate sheet so added another for the second sheet ( now had 2019 & 2020) and that worked. Added another - 2021.

After adding a new sheet and the simple statement, I needed to closed both spreadsheets, then Base worked for the newly added sheet.

[jagerlime]

Thanks for your answer. I forgot to mentioned that simple statment ='Counters.ods'#$'2020'.C2 works also fine for me in Base. But after your research I have done my and find a strange behavioru.

So I opened my Evidence.ods, scrolled down to find some empty cell and typed a formula ='Counters.ods'#$'2019'.A113 related to absolutly nothing so the value was 0. Saved the file and lunched Base Raports. Magically all previous formulas with INDIRECT related to file Counters.ods now have correct values. (!) What the hell did just happend? So I added another file, Payments.ods and linked data to Evidence in the same way as Counters. Opened Base Raports and… all cells from Payments was 0 but Counters works fine. So finally added in another empty cell =‘Payments.ods’#$‘2019’.A950` and the data was linked correctly in Base now.

So there is a problem to fetch data in Base with INDIRECT where sheet name is a variable. I have to have at least one cell linked manually to corresponded .ods file