What If…
What if, in a series of 100 workbooks all of the first spreadsheets had the same row 120? And this row had the same columns in all of the spreadsheets? Adding this row together across the workbooks would only require the information of the workbook names. There would be no mapping necessary, so all of the links for all of the cells would be the same, except for the workbook names.
What if all of the rows and columns in the first spreadsheet in all 100 workbooks were the same? With the cells all in a one-to-one relationship the spreadsheets could be added together in any combination of workbooks, with all the combinations using the same linking relationship.
If the spreadsheets are all identical, the linking relationships could be moved to outside of the workbooks, and the spreadsheet cells would not need any links at all.
The mapping of the bits and pieces of rows and columns to different rows and columns makes up the bulk of the effort required to work across workbooks.
A View from the Database Side of Things
Excel is a terrific tool. It holds numeric data in a precise matrix of rows and columns. From the database viewpoint, a spreadsheet is a series of records each with five fields; the workbook name, the tab name, the row, the column, and the value. Each data value is defined by a combination of four other fields.
If we have a database holding information for an organization with several levels, each record might appear as below:
image 1
The organization modeled by the database appearing as a company structure:
image 2
In our example, half of the fields used to organize, and make accessible, the data lies outside of Excel.
Excel + External Architecture = ExcelCube
If we regard these last five fields as a database record, we can add an external structure and create a very flexible data cube. By separating the organization from the data the structure can be changed by dragging and dropping, without changing the records or re-entering any data. This is the equivalent of redefining the fields in a database.
image 3
The ExcelCube structure equivalent to our database example is shown above. All of the levels are represented by an Excel workbook. The Excel icons denote input level Excel workbooks, and the folders denote Excel workbooks receiving and holding consolidated data from the workbooks below them.
With the structure external to the Excel workbooks, there is no need to import the Excel files into a proprietary environment, the files remain as stand-alone workbooks that can be opened in Excel at any time.
The existing skill set so widely used for Excel remains in use, and it is simplified to the extent that links for consolidation are no longer needed.
The ease with which the files can be added together means that the more input level Excel files there are, the more ways to add up and view the results. Breaking the model down to its lowest points of data entry serves the usefulness and simplicity of an application.