But, if the data in the first cell is deleted, then you can enter data in either cell again. Ok, that being said, is there another way to 'protect' a cell from having an entry put in it? Is there perhaps a function that would look at a cell, and if it had something in it, then any attempt to entr data in another specific cell would be inpossible, perhaps by just erasing what ever someone trys to put in there. I am not going to go and use a db just to get that. My need for protection, you can read, is not mission critical, but more for form entry control. Using a db would also require far too much programing, etc. I need the flexibility to add in new data and new calucations as I go.
![openoffice conditional formatting based on match openoffice conditional formatting based on match](http://datacandy.github.io/warwick/dataclean/clean4.png)
I am not keen on your push toward using a database - I know db's well, and that is way overkill for what I am doing, which is entering lots of unique data, with no particular structure, and then performing numerous calcuations base on those numbers. but of no one jumps in and says otherwise, I will take your word for it. I was hopeful because other posts say it is possible. Combining your clear 'its not possible' with one of your post up above, I now understand that you are sure its not possible. I have read a number of post on the topic, and some seem to say it should work, but it does not, just like it does not as in this thread. Is that at all clear?Īnyway, what I want to do is to be able to conditionally protect and unprotect a cell based on the input in another cell. BUT if I make the first entry blank again then the matching cell becomes unprotected so I can again choose which type of measure I input. What I want to do, is if I have entered a value in one then its no longer possible to enter data into the other. Thus, I have a cell for input of metric if that is what I have, and one for input for Imperial if that is what I have. I have data in Imperial and in Metric, and I need to convert which ever I have into the other and generate a list for both and these results are then used in other forumulas, some based on metric and some based on Imperial. I too have found that conditional formatting does not apply protection, at least consistantly. I would like to hear if there was ever a solution found to this. My intended solution was a lot more elegant-the protection was automatic and dynamic. That just winds up being a little time consuming. I regularly review the spreadsheets and, if need be, I can go into the affected sheets of each spreadsheet file and manually lock what needs to be locked. But the protection status of cells, as defined in a style, isn't affected when the style is applied via conditional formatting. Villeroy, you are correct, I had noticed that the listed style for the affected cells does not change even though the fill color changed, indicating the conditional formatting was working. I'm not really worried about this with my users. This is 99% for accidental changes any determined user can, of course, defeat the protection. I wanted to put this in place because I routinely goof up using the spreadsheet and I'm the one that wrote it. Fill color is also changed by conditional formatting to help the users keep up with where they are. As certain conditions are met the intent is to lock out previously filled cells to prevent them from being overwritten. The spreadsheet is being used by others to input incremental data over time. I apologize for being away over the weekend and not clarifying myself further. If the initial protection is to protect against malicious intent, then this is entirely the wrong way to go about that.ĭavid. I frankly don't see any other way, and see no reason why the person entering the data then protecting can not do it this way. The secondary protection is to stop any further data entry. The initial protection is to stop data entry into areas not intended. You enter that data then protect the entire sheet from anyone and everyone else? Why would someone intentionally misuse a spreadsheet if they were to be allowed to do that further protection? So far as I can see, my solution works for that purpose. I set up a spreadsheet with cells protected except for some of data entry. Entering wrong data is intentional misuse for example. There is nothing that can be done about intentional misuse if they can enter data. Kingfisher wrote:The objective is to set up the spreadsheet for others to use and to build in some protection against their accidental or intentional misuse.