sum from multiple identical structure workbooks

[bijalvp]

I have a folder called “source” which has sub-folders (currently 36 and these could grow). Each of these sub-folders has a .ods and ALL these files start with the same 4 characters - PRPL and then other text and/or numbers.

In the “source” folder I have another file named reports.ods and in this file I want to insert formulas to get the sum of certain cells from each .ods file in the sub-folder. So if I wanted to do this manually, I would enter in the reports.ods file for eg. in cell D6:

=‘file:///path-to-file/1/source1.ods’#$Assets.D6+‘file:///path-to-file/2/source2.ods’#$Assets.D6+‘file:///path-to-file/3/source3.ods’#$Assets.D6

and so on adding each files path and the cell reference (the same cell from every file)

This will obviously take forever and as I add sub-folders and more files, i will need to update the formula time and again

Is there anyway to have a simpler formula … something more cuboid like?

I mean is it even possible or will it need some code?

My version and other info:

Version: 6.4.0.3
Build ID: b0a288ab3d2d4774cb44b62f04d5d28733ac6df8
CPU threads: 4; OS: Linux 5.3; UI render: default; VCL: gtk3; 
Locale: en-IN (en_IN); UI-Language: en-US
Calc: threaded

Thanks
Bijal

[erAck]

For many functions that take cell range references like SUM() et al you can use cuboid references within one document. A solution could be to have one sheet per external file’s sheet, i.e. sheets Assets1, Assets2, … For more than one individual cell of a range creating the external references in such sheet can be eased by entering in Assets1.A1

='file:///path-to-file/1/source1.ods'#$Assets.A1

and then copy&paste the resulting formula cell to the desired target cells, e.g. D6, the cell reference will be adjusted as usual for relative references. For larger ranges it’s better to enter an array formula, for example

='file:///path-to-file/1/source1.ods'#$Assets.A1:D6

and close with Shift+Ctrl+Enter instead of just Enter.

The overall sum of your example then could be

=SUM($Assets1.D6:$Assets9.D6)

to sum cell D6 of 9 sheets. To sum cells D5:D6 of all sheets it would be

=SUM($Assets1.D5:$Assets9.D6)

i.e. you specify the front top left and rear bottom right corners of the cuboid.

To not having to adapt all formulas whenever you add a sheet, include one last empty sheet in all formulas and insert new sheets before that last sheet, so all references automatically include the new sheet. Same works of course if you insert a sheet anywhere between the referenced sheets.

[bijalvp]

I guess its not really gonna get too easy is it? Might have to think about creating a small sub-routine for eg to pick all files a folder and/or sub-folders starting with “some-fixed-string” and then pulling the data from the cells required for that particular formula which can be variable…

thanks… will try and figure what works best in the long run.