Excel Online - Protecting Formulas - Issues

New Contributor

Hello,

 

I understand that in order to protect cells and formulas from being overwritten that I need to change the settings on the desktop version of Excel. However, when I do apply these settings and then upload the file to OneDrive to be used in Excel Online (for collaboration), I run into problems.

 

Basically, users cannot enter information in any fields, regardless of whether those cells were protected or not. Is there a security option I am missing? Do I need to change something in the online version as well? I am tired of people overwriting my formulas and breaking the workbooks.

 

Any help would be greatly appreciated!

3 Replies

@mkhernandez79  I know dealing with protection is a newer feature in online excel and don't know how well it 'plays' with desktop settings.  Maybe just set the protection online:

mtarler_0-1662733953921.png

as you can see you can add ranges to allow editing.

Hi @mtarler, thank you for your help! I actually found that it would work but unfortunately, once I enact protection, it no longer allows auto-fill of a new row in a table. I know I can leave access open to add a row, but I have trouble getting people to sort by headers, let alone use the "Insert Row" command...Any way around that particular issue? Thanks again!! 

not sure. so if I'm understanding you have a table and if you lock some of those columns and someone tries to add a new row the table doesn't detect/add the new row or if it does it doesn't auto fill the formulas in the 'locked' columns to that new row?
and I don't know how the sort by headers fits in here.
as for the former issue, maybe you could use a button to run a script to insert the the new row in the table (i.e. unlock, insert, re-lock) for them to then fill in? if that is even the issue