Allow formulas working across multiple Excel files
A very powerful functionality which makes all database calculation programs unnecessary.
Simply put: allow formulas to work across multiple spreadsheetfiles.
Description: it’s already possible to use a formula in a spreadsheet file across multiple sheets in the same file. But for more complexed situations that’s not sufficient. For instance when there are (relatively) a lot of users, or a lot of formulas per sheet. It’s also not preferable to have the user data and the management info in the same file.
So the idea is that you can create a management spreadsheet file and then can use formulas which operate across multiple user files.
One way to implement this, is using templates:
An assigned person (e.g. a superuser) creates a template (e.g. “Revenue.template”)
Users create userfiles based on this template (e.g. “Revenue Januari Frank”, “Revenue Januari Roy”, “Revenue Januari Melissa”, etcetera).
The superuser creates a management sheet based on the template (E.g. “Revenue.management”). In this management file formulas can be used which operates across all the user files based on the same template
Calculating the sum of all the B12 cells in the differente user files. E.g.: TEMPLATE.SUM(B12:B12) or SUM(TEMPLATE(B12:B12)) or SUM(TEMPLATE.B12:TEMPLATE.B12)
Calculating the sum of all the B12:B15 ranges in the different user files. E.g.: TEMPLATE.SUM(B12:B15) or SUM(TEMPLATE(B12:B15)) or SUM(TEMPLATE.B12:TEMPLATE.B15)
Same for AVERAGE, TOTAL, etc...
Another way to implemente this, is that a (super)user select some (shared) files in his OneDrive on which he wants the formulas to operate. He can then give this collection of files a name (E.g. “Revenue collection”). On this collection he can then create a management spreadsheet in which he can use formulas which work across the selected files.