there’s always some numpty who insists on overwriting a formula, or inserting a row/column thus cocking up VBA cell references running in the background.

Named ranges would handle this. But there would be many other ways the incompetent would find to break it.

Numpties gonna numpt.

It is very tricky to predict all the ways people will break stuff when you know how it should be used, and even harder to get the balance of applying controls & keeping things functional.

one company my employers acquired had oodles of stuff on spreadsheets written by steve in accounts and modified by louise in purchasing without any checks and the results were biblical commandments with outputs resulting in pestilence and plague – first stage metals processing (laser cutting etc) most were inventory planning related, what customer part could be cut from what inventory item, how many parts, what other customer parts came from same item and could be nested, what if demand from customer Y increased/decreased and lead time for start stock was X months etc. Multiple related stock items – some customs bonded but physically identical, some different dimensions but could be used to produce same customer part etc.

not a techie but think ended up with some sort of java enabled overlays to the accounting/stock control unix based system that enabled easy entry of related product data/parts data/sorting/filters and the ability to run on screen what ifs with the actual logic coded away from fat fingers

here’s always some numpty who insists on overwriting a formula, or inserting a row/column thus cocking up VBA cell references running in the background.

You can stop this by locking cells, protecting worksheets etc. If you can edit stuff you shouldn’t, whoever designed the tool did a crap job. All methodology should be locked down so users can’t just randomly hack it – whether in Excel or any other tool.

NB A good book I can recommend on Excel VBA application design is..

You can stop this by locking cells, protecting worksheets etc. If you can edit stuff you shouldn’t, whoever designed the tool did a crap job. All methodology should be locked down so users can’t just randomly hack it – whether in Excel or any other tool.

I know this. However I work on a remote site and I’m only there for 3 weeks out of every 6. Work is soul-destroying enough that when I’m home I try to pretend it isn’t a thing, and I simply don’t answer any Aberdeen phone calls. If something did need changing in a spreadsheet I’d locked when I’m not there then they’d be screwed. It very rarely does, but there’s always that one time and so spreadsheets have to be unlocked.

I know this. However I work on a remote site and I’m only there for 3 weeks out of every 6. Work is soul-destroying enough that when I’m home I try to pretend it isn’t a thing, and I simply don’t answer any Aberdeen phone calls. If something did need changing in a spreadsheet I’d locked when I’m not there then they’d be screwed. It very rarely does, but there’s always that one time and so spreadsheets have to be unlocked.

As a life long avoider of any form of process / QA, I can’t believe I’m saying this, but the above isn’t an excuse, it’s an admission of a lack of any form of proper process and a risk to the business……

So I think my first demo will be a pocket money calculator for my kids. They get a basic £2pw and bonuses for chores. I’ll invent some complex rules for the purposes of demo e.g. £25p per chore but a bonus 50p for all chores, and maybe -50p for disciplinary measures.

I reckon I can knock that up in an afternoon using public cloud services, have the same data accessible by multiple clients and stored in a persistent location. All done without code or having to find a host and maintain a server.

More complex project for the second demo I think.

I reckon I can knock that up in an afternoon using public cloud services, have the same data accessible by multiple clients and stored in a persistent location. All done without code or having to find a host and maintain a server.

You’ve just discovered Google Docs?

it’s an admission of a lack of any form of proper process and a risk to the business……

Quite probably, but that’s not my problem. I am invested precisely 0% in the god-awful toxic environment that is where I work. I try to make my particular role a bit easier because of the whole “work smarter, not harder” thing and when some people see they get on board, that’s all. Some of those people don’t know what they’re doing, but again that’s not my problem.

You’ve just discovered Google Docs?

Not what I plan to do, no. But as always there are many ways to do things, each with pros and cons. And demos are by nature simple when they are dreamed out of thin air, and simple demos can always be implemented in different ways.

If you’re nice I might show you what I’ve done 🙂

go on, show us. \this is a good thread

Quite probably, but that’s not my problem. I am invested precisely 0% in the god-awful toxic environment that is where I work. I try to make my particular role a bit easier because of the whole “work smarter, not harder” thing and when some people see they get on board, that’s all. Some of those people don’t know what they’re doing, but again that’s not my problem

Indeed. Must be an industry thing.

We have shedloads of stuff written in Excel by various ex-members of various departments within our organisation, some quite complex. People get quite sniffy when we in IT don’t support/troubleshoot/bugfix/update this stuff for them.

Calculation of solubility curves, crystallisation DOE, assay determination.

Oh and booking equipment.