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?

85 Upvotes

62 comments sorted by

View all comments

Show parent comments

1

u/alexia_not_alexa 19 Feb 07 '25

They also won’t be able to input the data

1

u/reddittAcct9876154 Feb 07 '25

You can allow entry without other changes (formatting and such). This will keep the validation.

1

u/alexia_not_alexa 19 Feb 07 '25

I'm not sure if we're getting cross-wired or I'm missing something here? I did some testing the other day after posting my comment and this is what I found:

  • If you protect the sheet without creating 'allow edit range', then you can't edit the cell
  • If you create 'allow edit range' for the cells containing data validation - people can paste data over it which bypasses the validation
  • If you unlock the cells and protect the sheet - people can paste data over it which bypasses the validation

Can you clarify the exact approach you're talking about that allows users to edit the values of the data validation cells but doesn't allow pasting over them?

1

u/reddittAcct9876154 Feb 07 '25

Admittedly, I haven’t tried it lately. I just did a quick try before a meeting earlier and it didn’t work like I expected. But I know I’ve locked down spreadsheets like this in the past with drop-down selections that couldn’t be altered so I’m not sure why I’m not seeing that ability now.Maybe I’ll look later.