Well, this video has been a preview for a few weeks now – hoping to get some feedback from those interested in preventing spreadsheet risk.
None so far.
But the good news is, the clever people out there have spotted some ancillary ideas in this video. There’s plenty of interest from those who want to
- Share the same data among many spreadsheets
- Update a back-end table with selected updates
- Use a shared data model outside the spreadsheet(s)
- Selectively password protect sensitive data
So my faith in coolness of the Excel user community is restored.
I’m reminded of the story of 3M and the yellow Post-It tabs. Apparently, (I’m leaning towards this being a made-up story!) the 3M researchers were looking to produce an adhesive that will never come apart. But they failed. But it spawned a whole new incredibly successful product!
A logical sound approach to obtaining the business requirement of a single version of the truth. Will you be addressing the issues of: who populates the master data, data verification, data management organisation – because the spreadsheet is only one discrete part of the data management process.
Andy, having a single version of the truth is a start. Then all those things can be addressed. The data is no longer scattered all over the place.
My point is, the spreadsheet should be enabling the efficient workflow. But the data should not be trapped inside a spreadsheet file. It’s best (1) centralised and (2) accessible by spreadsheets that do the logic.
‘Who populates the data’ can be controlled by a Login. Although I mention it in this video I haven’t shown it. You can see it in other videos when they’re posted here.
Validation of results – We can have a flag on the spreadsheet that lights up if the grand total (on the spreadsheet) is different from the same SUM when aggregating the backend table.
Data management organisation – this is perhaps the key to a collaborative process. This is where all those involved in the process need to agree on what the workflow is, what data is manipulated by the spreadsheets, its integrity/security and so on. At the moment, people don’t think of spreadsheets as part of a bigger workflow. They should, and I find that this approach forces that thinking.
Awesome post!! Can you share and expand on how you achieve the Put action to write updates back to the Master file?
Hey Alex, it’s an UPDATE statement, just like the SELECT that gets the Get. I’m creating a free practice tool to Teach Yourself SQL. It’ll be ready in a few days.
Also, if you try the Celebrity Chef Spreadsheet Challenge, and if you like my suggested solution, there’s a mini-course to learn the principles.
How can I create a password to access the Celebrity Chef Spreadsheet Challange?
Hi Alex. It’s on a beta-program at the moment. I’ll email it to you now 🙂