Cell protection in OneDrive

Dear fellow forum members,

I have, in my opinion, a very peculiar (or rather annoying) problem. I work in the RE development & construction business, and we also sell the apartments ourselves. There is an Excel spreadsheet for the Sales department where all of the last few years' sales are kept track of (besides ERP but the good ol' Excel never fails you if the ERP does).

The file used to be kept on our internal company's server (connected through wifi or cable). The main 2 issues are that sometimes the internal server breaks down and sometimes people forget to close the Sales Excel file, which then blocks others from editing the file (you get a pop-up saying that someone else has locked the file for editing). So I, being a good citizen, decided to store the file on our company's OneDrive (cloud solution) so that even several people could edit the file at the same time. There are a) accountants, b) sales people, c) project managers and d) the business controller who can access the file. As there are several groups that can access the file, yet each group only has a handful of columns that they should actually edit (at least half are formulas), thus, I have used different colours for different groups of people (formulas also have their own colour).

Besides the colours, I have also locked all of the cells in the file and protected the main worksheet; however, I do use "Allow Edit Ranges" which allows me to make it possible for to allow people to only edit their respective columns which they belong to. Now, this worked just fine when the file was on the internal server. However, since I have uploaded the file to our OneDrive, some interesting things have started to happen regarding locked cells. For some reason... now everyone can edit everything. I was like WTF – it worked a day ago?! I started tearing the groups off from "Allow Edit Ranges" one by one so that I could make some sense of it. What I discovered was that the file on OneDrive does not differentiate between people in my organization – it treats everyone the same in our organization. For example, if there is at least one group under "Allow Edit Ranges" which can edit something and everything else is locked, everyone from our organization will still be able to edit the same cells. To be honest, I do not know what can I even do about this anymore, as this is a rather technical error not my misdoing.

However, I often get transferred to this forum when I am looking for Excel help/guidance so, thus, if there is anything to be done to fix this, it is here where I could find the answer.


P.S. The problem only persists when the Excel file is on OneDrive, a copy of the file would not be able to show you the problem, neither can I give you access to my company's cloud... Wish this were not the case.

Have a nice evening ya'll, it is 30min over my work hours and I am off!


Cheers,
K

BUMP? Perhaps someones has missed this thread..