Locking formulas while allowing data editing

[Scarlettrecords]

Hi, I am new to Excel and finding the other answers unclear as to this distinction. This is URGENT as our accounts info is overdue to our tax agent & I still have to enter it all.

I have created an income property accounts workbook with 12 worksheets, one for each month of the year. I have written in formulas so that all income and outgoings are automatically entered into the "In" and "Out" columns and so that all columns are automatically added up at the bottom of each sheet and those totals are carried through to the "year-to-date" cells in subsequent sheet.

I want to be able to input and correct data (rents in, expenses out) without being able to accidentally edit or erase formulas.

How do I do this?

 

[Scarlettrecords]

Just check to see whether "GROUP" appears in the Blue Bar right at the top of sheet at the end of the file name .
It will have the excel symbol followed by Microsoft Excel "followed by name of file".

If more than one tab has been selected this will show up as GROUP after file name.

To get rid of GROUP just click on one of the other tabs not selected .
If they are all selected right click on one of the tabs and select "ungroup sheets"

With a bit of luck that should fix it.

Pedro

Wow, that's great! Ok that allowed me to complete steps 1 thru 5 but then, rather than just protecting the formula cells that I highlighted, it's not allowing me to put any data in any cells. Why is that happening?

 

[pedro-egoli]

Is sheet Protected.

Go to Tools /Protection and if you see "unprotect sheet" click on that to unprotect sheet.

If you want to try Data Validation suggested earlier follow the steps.

Pedro

 

[Scarlettrecords]

Is sheet Protected.

Go to Tools /Protection and if you see "unprotect sheet" click on that to unprotect sheet.

If you want to try Data Validation suggested earlier follow the steps.

Pedro

That's strange. On the first workbook I followed those 5 steps (including 'protect sheet' and while it has locked the cells with formulas it still allows me to edit the data input in other cells, which is what I wanted all along. So I assumed that advice was sound.

But you seem to be saying that 'sheet protect' can be expected to block any data editing. I'm confused!

Yes I will try Data Validation now & will let you know how it goes. Thanks for all your help!

 

[Joe4]

That's strange. On the first workbook I followed those 5 steps (including 'protect sheet' and while it has locked the cells with formulas it still allows me to edit the data input in other cells, which is what I wanted all along. So I assumed that advice was sound.

But you seem to be saying that 'sheet protect' can be expected to block any data editing. I'm confused!

It is sound advice, and it does work, if done correctly.

You should be able to see this pretty easily.
Open a brand new blank Excel file.
In A1 enter 1
In B1 enter 2
In C1 enter =A1+B1

Now, follow those 5 steps I outlined.
You should see that you can edit A1 and B1, but not the formula in C1.

It sounds to me like you have some pre-existing details with your workbook that are complicating matters, whether it be Protection is already applied, Sharing, Grouping, other VBA code, etc.

 

[Scarlettrecords]

It is sound advice, and it does work, if done correctly.

You should be able to see this pretty easily.
Open a brand new blank Excel file.
In A1 enter 1
In B1 enter 2
In C1 enter =A1+B1

Now, follow those 5 steps I outlined.
You should see that you can edit A1 and B1, but not the formula in C1.

It sounds to me like you have some pre-existing details with your workbook that are complicating matters, whether it be Protection is already applied, Sharing, Grouping, other VBA code, etc.

Thanks Joel! That's reassuring. What steps would I take to investigate what pre-existing details are causing the problem?

Regarding your suggested possibility that 'protection is already applied' I just tried first choosing 'unprotect sheet', then doing the 5 steps, including protecting the sheet again at the end, but a message comes up saying "the cell or chart you are trying to change is protected and therefore read-only". So this seems to be saying that step 5 is the problem (although i understand that it should work as it did work on one of the four workbooks). Is there something else I should do to investigate whether protection is already applied?

I think I have addressed the sharing option - when I go to 'share workbook' it just shows me as using it (and I'm on a home laptop so that makes sense) and 'Allow changes by more than one user' is unchecked. Is there something else I should do to investigate whether sharing is the issue?

'Group' is not showing at the top of the page in the title line so does that mean the problem is not grouping?

I don't know how to investigate if it is 'Other VBA code', but as I am new to this & have only put in some formulas I would be surprised if I changed some underlying code - could a formula in a cell cause the problem?

I have linked the sheets in the workbook, but then I also did that in the workbook for which your 5 steps worked like a charm.

Have you any further suggestions?

 

[pedro-egoli]

Not wanting to add to the confusion but excel help "Locking only a few cells on a worksheet" shows procedure as

1.  Select the entire worksheet by clicking the Select All button (the gray rectangle directly above the row number for row 1 and to the left of column letter A). 
2  Click Cells on the Format menu, click the Protection tab, and then clear the Locked check box. 
This unlocks all the cells on the worksheet
 Note   If the Cells command is not available, parts of the worksheet may already be locked. On the Tools menu, point to Protection, and then click Unprotect Sheet.
3 Select just the cells you want to lock and repeat step 2, but this time select the Locked check box. 
4  On the Tools menu, point to Protection, click Protect Sheet, and then click OK.

See if this helps

Pedro

 

[Scarlettrecords]

Is sheet Protected.

Go to Tools /Protection and if you see "unprotect sheet" click on that to unprotect sheet.

If you want to try Data Validation suggested earlier follow the steps.

Pedro

Data Validation worked on those sheets that have some problem preventing me from succeeding with the other method. Thanks! I was a little nervous about using 'code', and worried that I might mess up something on some larger level, but was the easiest thing in the end. What are the downsides to using code? Why doesn't the Excel Help just tell us to do that instead of all the other faffing about?

 

[arkusM]

Then I am stuck until I can figure out why it is in 'unavailable grey'...

If you are still stuck with this...
You may not actually be seeing "unavailable grey", the check box is that colour because you may have mixed protected and unprotected cell. Clicking on the box will force all cell one way of the other.

Chances are if the sheet is locked you would not even be able to get to the format cells dialog box.

my 2cents.

DANG: Just saw that post #26 has the answer.

 

[Scarlettrecords]

If you are still stuck with this...
You may not actually be seeing "unavailable grey", the check box is that colour because you may have mixed protected and unprotected cell. Clicking on the box will force all cell one way of the other.

Chances are if the sheet is locked you would not even be able to get to the format cells dialog box.

my 2cents.

DANG: Just saw that post #26 has the answer.

Thanks though, you make a good point!