I'd like to do a sum or sumprod over cells across different files. More precisely:
I have a list of file paths to other .ods files which contain data organized in the exact same way. Say in cell A1 'file:///C:/Users/jean__000/Desktop/data1.ods'#$Sheet1
, ..., in cell A9 'file:///C:/Users/jean__000/Desktop/data9.ods'#$Sheet1
). In cells B1, ... , B9 I have a boolean representing whether I want the corresponding files to be included in the calculations or not. I now want to compute the sum, over all files which are set to TRUE, of a given cell (say, C1). If this was just a sum across sheets I could use a sumprod of indirect of the given range, but I can't find how to designate a range across different files.
I could add a new column (say C), and have data1.ods#$Sheet1.C1
appear in C1, ... , data9.ods#$Sheet1.C1
appear in C9 ; and then sumprod over that, but I'd like to avoid creating useless columns.
This is a follow-up question from Replicate data from separate files in OpenOffice Calc
No comments:
Post a Comment