Streamline workbook

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Hello, I am hoping you can be of assistance to me.


    The attached workbook is a schedule of church workers for both nursery and children’s church.


    On the “Schedule” sheet, the range A1:C22 is what I have for Nursery workers and the dates they are scheduled.


    E1:H22 is the range for Childrens church workers and the dates they are scheduled.


    I2:N22 show the dates that some of them are not available.


    A24:A33 are the names of the Nursery workers.


    The “Data validation” sheet is my attempt at beginning the process, but I just don’t know where to go from there.


    I would like to streamline this workbook in a couple of ways.

    • To be able to select from a list of names of teachers and assistants (which sounds like it would be data validation)
    • To keep from scheduling a person on a day that they indicate they are unavailable. (Maybe an error alert)
    • To keep from giving someone double duty in both Nursery and Children’s church on the same day (Again, maybe using an error alert).

    Any help you can give would be great.


    Thank you,


    Randy

    Hi,


    Attached is your initial revised file with all data validations


    Regarding your scheduling, how to do you input dates ?


    In order to come up with error messages after checking who is available when ... this would normally require a macro


    Are you reluctant to macros ?

    As far as imputing dates, I guess we would just put them in.

    I am not opposed to using marcos, but don't know how to do it.

    When I mentioned an error alert, I was thinking of conditional formatting or data validation.

    You are welcome

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Thank you for helping in this. Sorry it too so long to get back to you. What I am trying to do is to be able to select from a list of people (you've done this already.) Also, to be able to have Excel look at the list (columns I, K, M) of dates that people are unavailable to work, then alert the scheduler of this discrepancy if imputed, either by conditional formatting or a macro. Also to alert the scheduler if they schedule Amy on both nursery and Chrildrens church on the same day.

    Hi again,


    You have designed your Schedule worksheet in such a way that it completely prevents any date verification (as you would like it) ...


    As an example, take cell C4 ... this date is common to both Carrie and Olivia ...

    so how should the Check and/or the Warning function individually for each person ???


    Generally speaking, you seem to be tempted to use Excel as you would be using a word processor such as Word ...


    When it comes to Excel ... Cosmetics, Reports, Prints, etc... should be designed in their own separate worksheets and populated by sheets which contain the serious stuff : your Database and sheets which perform all the types of Analyses you might need ...


    By mixing up all these different characteristics, you are planting the seeds of all your present and future problems ...


    Hope above explanation will help you out ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Thank you for the feedback. I don't want to use it as Word, but Excel, so I will take your suggestions. Can I resubmit it to you when I do what I can?

    Hi,


    What I am gathering from your various comments is that your actual Starting Point is the Selected Day ...


    Secondly, for this specific day, you need to know Who is Available (and who is not...) in order to assign and schedule your tasks.


    In addition, probably because of the intrinsic nature of the different tasks (which are not listed), you do need to take into account the role and function of each person.


    As a consequence, from a practical standpoint, this would suggest a complete overhaul, which would result in a framework where you could rely on several dynamic data validation lists constantly updated by your Forecast Calendar of Absences ...


    Feel free to share your comments

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Hi,


    When it comes to Data Validation for your Names, would you rather have one List with all the Names or several lists built specifically for each function / role ?


    There a couple of persons who apparently can intervene in different roles ....

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Hi again,


    For your review, since Sundays are the crucial point, have added a worksheet: Days Off


    In this sheet , you could add in Columns A and B the dates and names of the persons who will be off.

    By selecting a Sunday date in cell E2, you will get automatically :

    - In column F the list of the persons Off on this specific date

    - In Columns G,H,I and J the Names of the persons available by function, as well as the ALL recap

    - the first 3 columns G.H.I are designed to be used as Data Validation in your Schedule...


    The columns Q to V show an automatic Sunday Yearly calendar by day with the Names of the Persons not available


    The Columns X to AH show a Pivot Table to have a Count Analysis


    The Columns AJ to AQ create an automatic generic Yearly Calendar (by Quarter by Month) - just input date in cell AJ1


    Hope this will help :)

    No problem ...


    Take your time to review the worksheet ... and determine if it could be considered as a starting point ... :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    First of all, this looks amazing! You have clearly put a lot of effort into this workbook. I can only hope to someday understand Excel as well as you do.

    A couple of questions though.

    1. On the "Days off" sheet, what is the purpose of column C? I can see that it changes depending on the contents of E2.

    2. You said, "you could add in Columns A and B the dates and names of the persons who will be off." Does this mean we can add to column A and B to accommodate for subsequent Sundays going into 2024?

    3. Because of columns G,H, and I, it looks like I can delete the "Data validation" sheet. Is that correct?

    4. Using Amy and Rosi in Childrens church, I'm noticing that if I put them in on May 7, there isn't anything telling me that a mistake has been made. Is there a way of alerting me (or the scheduler) of the problem other than going to the "Days off" sheet?

    5. On the "Nursery" sheet, there isn't a drop down for column C.


    All around, this is tremendously put together.


    Thank you so much,

    Randy

    Hi Randy,


    Glad to hear this could help you out :)


    Let me tackle your interrogations :

    1. On the "Days off" sheet, picking a date in cell E2 will modify the whole of column C in order to re-generate a "Yes/No" feature, which, in turn, impacts all your Data Validation Lists to have them adjusted by the Days Off

    2. As far as next year is concerned, it is fully automatic , just update cell Q2 and cell AJ1

    3. Yes, you can delete the sheet "Data Validation"


    As far as the other points are concerned, I did not work on these issues ... as I was waiting for your approval on the first step ... :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Great ... so, in terms of streamlining, understand we can move on ... and tackle step 2 ...


    Will get back to you as soon as feasible :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Quickly revisited your first two worksheets ... (quite honestly, the "Master schedule" is a total mystery to me ...)


    Regarding your two inputs sheets :


    1. NEVER use Merged Cells ... they are the seeds of all your future problems ...!!!

    2. Selecting a Date in Column A will automatically adjust All the Data Validations Lists to select Names


    All previous modifications have obviously stayed in place, and you can use Column A & B in the sheet "Days Off" to input your data


    Hope this will help :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login