Don't know how to name the issue, trying to systemize data entry

[SmileItOff]

Hello Misters and Misses,

Happy Friday and Happy Holidays!

I am handling quite a lot of spreadsheets/trackers that are in the share folders and they are constantly being edited. It gets to a point where it is confusing in terms of the accuracy of the numbers in the spreadsheet because they are way too easy to change, way too many hands on the sheet, and no way to track the edit history.

Is there a way to systemize the data entry with excel Marco/Formulas/Templates? It would be more convenient to stick with excel so that people don't need to learn a new program.

Thank you very much!
Sme

 

[Jerry Sullivan]

Hi Sme,

There's a wide range of steps that can be taken to improve consistency of data collected from many users.

Two relatively simple steps that you can setup are:
1. Lock all cells except those that the user is allowed to modify. Then protect the worksheet to have the locking apply.

2. Use Excel's built in Data Validation, to only allow entries that match certain criteria (e.g. Number that is greater than 0 and less than 100).

More sophisticated approaches can be done with VBA, but the two steps above are a good way to start.