New worksheets - Copied Formulas from Existing- Protected from Non-Excel users input

I am in desperate need of help as I know just enough about Excel to know how much I don't know and I am under extreme pressure to accomplish this task very quickly.

Situation:

• For years we had a bookkeeper with experienced with Excel who only handled maintaining workbooks/worksheets for our salesmen's commissions. We would supply her with the data and she would input and provide us with up to date workbooks.
• The salesmen we had are no longer with us
• We cannot afford to out source this task.
• We have a new salesman who's jobs and commissions paid and outstanding need to be tracked.
• The people who will be inputting the data have absolutely no understanding of how Excel works or the concept that formulas are reacting to the data they enter.

I have the worksheets with the layout, formatting and formulas that were used for previous salesmen.
My thinking is that if I can copy those worksheets with the formulas and figure out how to protect the cells with formulas while leaving the "data entry" cells un-protected I may be able to make this workable.
The worksheet would also have to allow for new rows to be added as new jobs are sold that would then continue the formatting/protection of the previous row while maintaining the formulas contained in the last rows for running totals, etc.

Thank you in advance for any help or direction you may be able to offer!!

Chris:

Hopefully I followed your directions for the attachments properly! Thank you for including them.

The salesman won't have access to the workbook, so I am not worried about that. As far as someone working around the protection, the people using workbook think an Excel worksheet is a "program", don't even understand that they are actually using Excel much less understanding anything "going on" within what they are in-putting the data in.

I basically need a tool for them to insert data that is dummy proof. Left to their own devices they will click on a cell they don't belong in and hit "enter" and then wonder "why the program doesn't work anymore".

Thank you for responding. You have no idea how much help is appreciated at this point!

Hi Trulife76

have a look at protecting your worksheet.

How to Lock Individual Cells in a Worksheet

How to Protect Cells that Contain Formulas in Excel

Originally Posted by dogberry

Hi

As they are not excel users all I have done is hide the column for you. If they cant see it they cant alter it. To show it again all you have to do is highlight your columns A B C D E etc right click and select unhide.

Chris

Thank you Chris.

The problem is that the people filling in the data are the same ones that need to see and track the commission status. Unfortunately I'll never get them to "hide" a column to protect it from themselves and then "un hide" to gather the resulting information. It is so bad that I'm afraid that if I try to teach them the process they will highlight the column and before hiding it will accidentally wind up deleting it. I've tried teaching them "undo" to no avail.

To put things even more in perspective, they think AOL is the way to surf the internet.

To make matters worse I am typically working 4 hours away and am only in the same office occasionally.

Originally Posted by Kevin UK

Hi Trulife76

have a look at protecting your worksheet.

How to Lock Individual Cells in a Worksheet

How to Protect Cells that Contain Formulas in Excel

Thanks Kevin those links are helpful.

Question: If I lock/protect cells and the person doing the data entry needs to add additional rows as jobs are sold, is there a way to have the formatting/formulas continue with the added rows?

Any help is greatly appreciated!!!

Hi Trulife76

I am not sure on that. But if you unlock enough cells/rows that should be OK. As you lock the cells/columns with the formula's in. Hope that makes sense!

Kevin

Originally Posted by Kevin UK

Hi Trulife76

I am not sure on that. But if you unlock enough cells/rows that should be OK. As you lock the cells/columns with the formula's in. Hope that makes sense!

Kevin

Thanks Kevin. Scary thought....yes it makes sense and I'm in the weeds.

Trying to claw my way through this. As I mentioned earlier, I know just enough to know how much I don't know (but want to learn) and the people I'm creating for barely know how to turn on their computers. (Sometimes I wish they didn't know how to do that!).

Thank you.

Hi Trulife76

I don't know if you have it sorted. Have a look at your spread sheet attached. everything is locked except for columns A:C & E:J.

If you have it sorted, then ignore this.

Kevin