Forum Discussion
Question One: Protect Format & Two: Formula
I am using Microsoft Excell on a Mac computer. I have created the attached 4 Week Microsoft Excell Spreadsheet Calendar Template, which includes calendar dates. I use the calendar repetitively as a template.
Question One: Once I get everything set up the way I want it, I would like to know how do I protect the format so that cell sizes and boarders cannot be changed but would allow my secretary to input data (names) into the cells?
Question Two: Notice in cells A1 and A16 that they are grammatically incorrect i.e., “4rd” and “1rd”. What do I need to do to the formulas in A1, A16, A31, and A46 to a sure that It will return the creamer correct grammatical statement i.e. “1st”, “2nd”, “3rd”, “4th”, regardless where any particular week shows up?
4 Replies
I changed formulas to be:
$A$1 = =(INT(DAY(C1)/7)+1) & CHOOSE(INT(DAY(C1)/7)+1,"st","nd","rd","th","th")& " SUNDAY WEEK 1"$A$16 = =(INT(DAY(C16)/7)+1) & CHOOSE(INT(DAY(C16)/7)+1,"st","nd","rd","th","th")& " SUNDAY WEEK 2"$A$31 = =(INT(DAY(C31)/7)+1) & CHOOSE(INT(DAY(C31)/7)+1,"st","nd","rd","th","th")& " SUNDAY WEEK 3"$A$46 = =(INT(DAY(C46)/7)+1)&CHOOSE(INT(DAY(C46)/7)+1,"st","nd","rd","th","th")&" SUNDAY WEEK 4"Maybe there was some incorrectly placed "(" / ")".
As far as protecting, I created 4 named ranges:
Then I changed the Format Cells > Protection; unticked 'Locked'.
Lastly, I only allowed selection of 'Select unlocked cells':
You could do with VBA but I think this will be easiest to implement.
- Thanks so much!!!
Now I have one more question. I want to leave Cell C1 unprotected because it is the beginning input date cell for all other dates. Can you illustrate the workflow for the above? Then I will use it as a template going forward. Again, thanks so much!With the file I provided, you'll need to Unprotect the Sheet > Select Cell C1 > Format Cells > untick Protect checkbox > Protect Sheet.
