Lock cell for recalculation by checkbox

[RJberg]

Hi, What i would like to to is to lock my cells based on the value of the checkbox (true / not true).
I create a sequence of numbers and based on the checkbox it has to appoint the next number to a cell.

example.ods

The axample shows what i would like to do i 3 steps.
The “YES” will be the checkbox.
As you can see the sequence number is not added top down but more or less randomly.
I want to be able to lock the “New registrtion number” after i added the number so it won’t be changed the next time i open the document.

I am able to do this in Excel,but i find the programming in Calc a bit more unclear and the excel file is not working in Calc.

Hope my question is clear.

[Wildcard]

Can you give a bit more clarity on what you’re trying to accomplish? The file you attached doesn’t include any formulas at all. It’s just numbers and text entered in a grid. Wouldn’t you like something to be automatically calculated (or figured out, whatever) by the spreadsheet? If so, what?

[RJberg]

Ok, I tried to make an example with some cell filled.

example.ods

In addition is it possible to grey out the checkbox after it is checked and the form save so it can’t be changed later on?

[Wildcard]

It looks like you want a one-way operation to easily lock the contents of a given cell (but not to unlock them). I can’t see any other function that the checkboxes are supposed to perform.

Firstly, I will say that there is almost certainly a much, much easier way to accomplish the basic task you are trying to perform here. I say this because your question is far from the usual requirements for an Excel or Calc sheet. If you give more information on what you are basically trying to accomplish, it may be possible to recommend a much simpler route. (i.e., what were you trying to get Calc to do that led to the need for these checkboxes? What was the original, simple idea of what you wanted the computer to help you with?)

However, I will attempt to answer your question as asked.

The only way I am aware of to lock a cell’s contents so they cannot be edited is by protecting the sheet it is on. You do this by selecting Tools->Protect Document->Sheet… and clicking OK. (The default settings are usually what you want.)

By default, this will mean ALL cells on that sheet are protected. It is more usual to need to UN-protect one or two cells than to need ONLY to protect one or two cells. However, you can get around this. You just unprotect all of the cells before you protect the sheet:

1. Select the entire sheet. (Click in the upper left corner of the sheet, just to the left of “A” and just above “1”.)
2. On the menu, go to Format->Cells…
3. In the “Cell Protection” tab, uncheck the “Protected” box.
4. Click OK.
5. Select Tools->Protect Document->Sheet…
6. Click OK.

The sheet is now ready for use.

When you want to protect a particular cell:

1. Go to Format->Cells…
2. Check the “Protected” box in the “Cell Protection” tab.
3. Click OK.

You will not be able to change any cell back to “unprotected” without unprotecting the entire sheet first. You can set a password for the sheet protection, if it’s important to have your “individual cell protecting” functionality be truly one-way.

(If this answers your question, please click the green checkmark.)

[RJberg]

Thank you for Your help.

My idea is simple i want to use a sequence 1,2,3,4… to be assigned to a list of numbers, but this should not be added top Down, but more randomly. It will be a invoice number system. This mean that when i get orders in and registred in my sheet these are not invoiced in this order. So order 2015-02 will not necessarilybe invoiced after 2015-01, but perhaps before. I tried to do this by finding MAX value, but this didn’t work, since the Invoice number has to be locked

[RJberg]

PART 2: I should not be able to change the number afterwards unless i do this manually or something similar. I didn’t mean to make the question more difficult for you, but i thought it would be easier to ask the question without the Whole invoice sheet. The above solution does not seem to protect from recalculating the value so this solution doesn’t work for me. any tips.

[Wildcard]

I think I understand. Why not just have the invoice number entered manually? Or, if you like, copy and paste it from a single cell “next invoice number” which will automatically show the next invoice number. That way you don’t need to mess around with locking cells.

It seems you are trying to use “locking cells” to prevent recalculation, not to prevent editing by the user. That is different. The best way to prevent recalculation is to enter the data as a static value, not a formula.