Can I deactivate the calculation for specific cells or sheets?

Hello everyone

I am currently working part time as a caretaker for a guy with cerebral palsy. He has a total of 8 employees for which he always does the shift planing by himself. as he is heavily physically impaired, I decided I want to try to simplify the process for him.

Luckily, all of his employees have certain specific shifts they usually get. so in theory it would often be possible to just copy a weeks plan into the next one and just put in some small adaptations. but I wanted to go further than that, because this is still pretty difficult for him to do, which costs him a lot of time and effort and leads to an unnecessary amount of mistakes.

so what I did is that I put the "Standard week plan" with the usual shift distribution. then I simply wrote a code that checks for the weekday of a specific date, looks at the time of the day the shift is meant for, and then copies the corresponding field from the standard plan. this way (and with a few other minor things), he only has to change the month on top of the sheet and it automatically puts in the standard shifts for the whole month. he then only has to correct the ones where the planned employee is missing and has a replacement. so far so good. works like a charm and was not that difficult to do.

but now there is one more little problem. lets say he creates the plan for november with this method and then hires a new employee starting december. he will then adapt the "Standard week plan" according to the new standard schedule. that is pretty easy and makes him have the december plan done right away aswell. but it will also change the novemberplan retrospectively, which could mess up his documentation of the previous months.

so, long story short: I am trying to find a way to lock a sheet so it does not change anything when the page it refers to is changed. is there any option to do that?

best wishes

Juerg

Juerg,
Here is one option:
On a separate worksheet list employees and shifts for week (see below).
Column A Column B Column C Column D Column E Column F Column G Column H
Row 1 Name Saturday Sunday Monday Tuesday Wednesday Thursday Friday
Row2 Person1 Start - End Start - End Start - End Start - End Start - End Start - End Start -End
Row3 Person2 Start - End Start - End Start - End Start - End Start - End Start - End Start -End
Row4 Person3 Start - End Start - End Start - End Start - End Start - End Start - End Start -End
Row5 Person4 Start - End Start - End Start - End Start - End Start - End Start - End Start -End
Row6 Person5 Start - End Start - End Start - End Start - End Start - End Start - End Start -End
Row7 Person6 Start - End Start - End Start - End Start - End Start - End Start - End Start -End
Row8 Person7 Start - End Start - End Start - End Start - End Start - End Start - End Start -End
Row9 Person8 Start - End Start - End Start - End Start - End Start - End Start - End Start -End

Next Highlight all the above and select table with Header, call it Name.

On another sheet enter everything in Row1 (as the heading).
On this sheet, in Column A, cell 2. Use "Data Validation" that way you limit what can be entered (Only Column A information).
On the same sheet, Column B thru Column H: Enter =VLOOKUP from the saved table selecting one Column at a time) to enter the information from your table.
There is a youtube video that may help explain it better.
Link:
https://www.youtube.com/watch?v=eFuOjAtDu0E

Hello

If I understood that right, he would have to chose a name for every shift from a dropdown menu with your solution?

I think I found a pretty simple way to do what I wanted to. Instead of having a seperate sheet with the standard work week plan, I simply put that plan on the monthly sheets, below the months plan. that way,the codes in the table always refer to the correct "standard plan" that is in place during said month. also, when he starts planning a new month, he just copies the sheet of the previous one and changes data in the new sheet. now with the way I have set it up, he still copies it and then only changes the data in one single cell, then it should automatically create a correct plan (except if some shifts change of course).

Thanks for your help either way, but I think I will go with my solution because it does exactly what I wanted, eventhough it might not be the prettiest option. the printout will still look the way it is supposed to, though

best wishes

Juerg