Spreadsheet Process Automation
The Spreadsheet
Since ancient times, humans have structured data into columns and rows. This is called a table. Read more about the spreadsheet, its concept and terminology from Wikipedia or many other sources.
The Problem
With such a simple and flexible tool as the spreadsheet, what could go wrong!?
In the design of your spreadsheet tables, technical challenges can quickly arise. For example, if you start building a table to represent your product or service orders. Soon you will need to add specific items within that order, and where should those many items go in the table? Should you list them in a single cell, create many columns, or relate each order to many rows of a new items table? This is a simple relationship of one to many, but within a spreadsheet there are many new challenges that arise with table relationships that may require formulas like VLOOKUP.
The spreadsheet’s simplicity has come to haunt the user that was initially excited by it. Their tasks now seems a massive engineering task, that they must buy expensive software for.
The Solution
Often there is a well built software solution to accomplish the tasks you need to complete. For many software solutions, these tasks will be pre-defined and difficult to change. Usually the tables of the spreadsheet are locked, and the relationships pre-defined for you. For very complicated tasks, this is often the best solution. This solution comes with its own challenges of piecing together the right set of software to accomplish your unique business needs. Some software will have overlap with others, some software will have gaps that you are not allowed to fill.
Alternatively, with modern spreadsheet tools a small amount of code can be inserted into the spreadsheet, to accomplish more difficult formulas and processes. Like many software solutions you can integrate your other systems data into the spreadsheet, but maintain the flexibility and simplicity for your unique process. This hybrid approach comes with many advantages of a spreadsheet, with enhanced capability to accomplish complicated tasks.
Tools
Some of my favorite tools for a hybrid spreadsheet solution: