Excell Format Protection and Formula Question.

[Richard_James]

As you know, 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?

[#2]

@Richard_James 

1) the feature is under review tab on ribbon, called Protect Sheet. When you select that, you will be presented with a bunch of options on what users can and cannot do, including formatting. To ensure that users can enter data, you will want to go to the cell properties of the cells where you want them to be able to enter data, and on the Protection tab of the cell properties window uncheck "locked" BEFORE you protect the sheet. When you protect the sheet, while protected, only those activities that were permitted can be done. You also have the option to set a password to unprotect if you have curious users - otherwise they can easily just unprotect the sheet themselves. 

2) There is no simple answer to formatting the ordinal suffix, in my experience. You can either come up with a complex formula to deal with all the various possible outcomes and append the suffix, or just go without it, like "SUNDAY #4". If you have a pretty finite list of positions, a simple lookup table will be the simplest, containing two columns, one with integers, and the other the string counterpart with correct suffix. (and obviously, you can hide this from view so it's doesn't clutter your interface - I use these kinds of reference tables all the time, and usually just stick them on a Reference sheet and hide the sheet, calling them by formula only. 

If you google "excel ordinal formatting" you'll get all kinds of options, all of which are various levels of ugly. 

[#3 · Accepted solution]

@Richard_James 

I made changes to your formula and used this instead. For example for cell A1:

=IF(DAY(C1)-WEEKDAY(C1)<7,"1st",IF(DAY(C1)-WEEKDAY(C1)<14,"2nd",IF(DAY(C1)-WEEKDAY(C1)<21,"3rd","4th")))&" SUNDAY WEEK 1"

and after that I just copied and paste it to the rest of the cell A16, A31, and A46 and only change the last part of your text string to WEEK2, WEEK3, and WEEK 4.

 As for protecting the worksheet, all you need to do is highlight all the cells that could only be modified and any additional cells you need to highlight just hold crtl key. Once you're done right-click and select format cells and uncheck locked. After that, you can protect your worksheet. By default, all cells are locked unless you unlock them. 

[#4]

Thanks!!!

[#5]

Thanks Mark!!! You are for the best response.