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]

Protecting the workbook won't affect locked/unlocked cells. It's protecting the sheets that does it.

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

 

[Scarlettrecords]

Strange. Do you have other Macros on the workbook or AddIn that may be greying out the Protect Sheet Menu?

Hi, I'm struggling to answer this question. How do I find this out?

 

[pedro-egoli]

Why not use Data Validation just for those cells containing formulae.

To do this highlight columns/rows with formula and then go to Data/Validation .

Under "Settings" select Custom and under formula enter (including the "")

"0>"

Put in "input message" and "error alert' to alert users that there are formula in cell if they try and enter info .

If sheet is protected go Tools/Protection /unprotect

Pedro

 

[Scarlettrecords]

Why not use Data Validation just for those cells containing formulae.

To do this highlight columns/rows with formula and then go to Data/Validation .

Under "Settings" select Custom and under formula enter (including the "")

"0>"

Put in "input message" and "error alert' to alert users that there are formula in cell if they try and enter info .

If sheet is protected go Tools/Protection /unprotect

Pedro

Hi Pedro & thank you for your message. I cannot unprotect the sheet. When I go to Tools/Protection there are two greyed unavailable options, "Protect Sheet" and "Allow Users to Edit Ranges" and two black availabe options, "Protect Workbook" and "Protect and Share Workbook".

 

[ISBB]

Followed the instructions myself and they worked a treat.. Thx for the tips fellas..

 

[pedro-egoli]

Sounds like the cells might be locked as suggested earlier

How do I unlock a cell?
Select the cell or cells that you want to unlock.
Choose Format - Cells
In the Format Cells dialog box, click the Protection tab
Remove the checkmark from the Locked checkbox.
Remember: Locking or unlocking cells has no effect unless the worksheet is protected.

Alternatively workbook might be shared check Tools/Share Workbook and see if this is the case.

Pedro

 

[Scarlettrecords]

Why not use Data Validation just for those cells containing formulae.

To do this highlight columns/rows with formula and then go to Data/Validation .

Under "Settings" select Custom and under formula enter (including the "")

"0>"

Put in "input message" and "error alert' to alert users that there are formula in cell if they try and enter info .

If sheet is protected go Tools/Protection /unprotect

Pedro

Hi, I can lock and unlock cells, just not the sheet as the latter is greyed at all times. Also, I was unable to find "Settings" - where is that?

 

[pedro-egoli]

When you go to Data/Validation the drop down window that opens shows "settings" ,"input message' and "error alert".

In the settings window under "validation criteria" -"allow" click the arrow to right of box which shows 'any value' and select Custom.

Re my later comment

Alternatively workbook might be shared check Tools/Share Workbook and see if this is the case.

Is workbook shared.

Pedro

 

[Scarlettrecords]

When you go to Data/Validation the drop down window that opens shows "settings" ,"input message' and "error alert".

In the settings window under "validation criteria" -"allow" click the arrow to right of box which shows 'any value' and select Custom.

Re my later comment

Alternatively workbook might be shared check Tools/Share Workbook and see if this is the case.

Is workbook shared.

Pedro

When I check Tools/Share Workbook the "Allow changes by more than one user at the same time" box is not ticked and it only shows me having the workbook open. I'm guessing that means that the workbook isn't shared.

When I go to Data the 'Validation' option is in grey and unavailable.

What should I do next?

 

[pedro-egoli]

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