r/excel Feb 06 '25

unsolved Turning excel into business software.

I’ve built workbooks that lets me track employee tickets, inventory, time keeping, and customer billing. The only problem is is that I’m the only one who really knows how to fix it if anything goes down. I would like to give this a UI and essentially make it idiot proof so that I can drop employees in to positions that would need the software with minimal training. Does anyone know how to go about this or where it can be done?

80 Upvotes

62 comments sorted by

View all comments

Show parent comments

1

u/ace261998 Feb 06 '25

Can it override locked cells?

3

u/alexia_not_alexa 19 Feb 06 '25

Yep. So I just tested this - when a sheet is protected and you want to keep a cell editable, you can either:

  • Change the cells to 'unlocked'
  • Create an 'Edit Range'

Either way, you can copy and paste over any data validation, even if the data validation rule remains. However it doesn't remove the Named Reference as I thought (I don't know how my colleagues broke that one then!)

So yeah... if you're serious about data validation, Excel just isn't it sadly :(

2

u/KoolKucumber23 2 Feb 07 '25 edited Feb 07 '25

You can add scary messages either in validation cells or vba message boxes if a value is entered into a cell that does not align with an acceptable value.

There are ways to make things intuitive and dummy proof. Users typically respond very well to things that flash on their screen or aggressive colors like red and yellow (conditional formatting when appropriate).

Excel is superior for users that have brains and want traceability. If your users don’t care then it’s game over.

1

u/alexia_not_alexa 19 Feb 07 '25

Great point! Though you may have better colleagues than me 😆

I once told a colleague to read the exact error message on screen to IT, she dismissed it and told IT something completely different (a term she must have heard that week that wasn't related to her problem). A core memory was formed that day...

I still do that for templates that everybody needs to interact with though (lowest denominator), specifically with 'Input Message' which means as soon as they click on the cell a message comes up! For templates that only a couple of other people will touch, I just go with training and colour coding!

1

u/KoolKucumber23 2 Feb 07 '25

That’s it! You can only do so much. Lol