+ Reply to Thread
Results 1 to 17 of 17

Formula for male and female staff on rota

  1. #1
    Registered User
    Join Date
    07-18-2013
    Location
    Liverpool
    MS-Off Ver
    Excel 2010
    Posts
    28

    Formula for male and female staff on rota

    Hi

    I have a section in my rota that calculates how many staff are working on each shift but I would also like to see how many male and female staff are on. My rota is for the care home I work for and at least one male member of staff must be on shift at each time so this would be very useful.

    Could someone give me some guidance?

    Also, I have created this rota on Excel 2010 but our office pc is 2007 and when on the office pc the drop down boxes etc are not working. Could someone tell me how I make this compatible with 2007?

    Many thanks in advance

    Tiger
    Attached Files Attached Files
    Last edited by bigtiger1; 08-02-2013 at 05:12 AM.

  2. #2
    Valued Forum Contributor wenqq3's Avatar
    Join Date
    04-01-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2003
    Posts
    868

    Re: Formula for male and female staff on rota

    Hi bigtiger1,
    Can you insert some data, all is showing zero. And lets us know, the expected result is located at?
    Please manually do some sample result. So we can just write a formula inside the worksheet. Thanks :D
    -If the problem is solved, please mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved".

    -Always upload a workbook before start your question
    To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

    +++ If my answer(s) helped you, please add me reputation by click on * +++

  3. #3
    Registered User
    Join Date
    07-18-2013
    Location
    Liverpool
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: Formula for male and female staff on rota

    Hi, revised rota attached. I have highlighted where the staff numbers need to go. I have also put some shifts in (through the dropdown boxes i created)

    Thanks for your help

    Tiger
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor wenqq3's Avatar
    Join Date
    04-01-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2003
    Posts
    868

    Re: Formula for male and female staff on rota

    Staff Schedule (EDIT).xlsx

    Please check, is this okie for you:D

  5. #5
    Registered User
    Join Date
    07-18-2013
    Location
    Liverpool
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: Formula for male and female staff on rota

    Hi this is just what I am looking for, thank you so much!

    Do you know how I can make this compatible with Excel 2007 as when I send this to my office pc the drop down boxes etc do not work?

    Tiger

  6. #6
    Valued Forum Contributor wenqq3's Avatar
    Join Date
    04-01-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2003
    Posts
    868

    Re: Formula for male and female staff on rota

    I'm using 2007 as well.
    Where is the drop down box?

  7. #7
    Registered User
    Join Date
    07-18-2013
    Location
    Liverpool
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: Formula for male and female staff on rota

    I have created drop down boxes next to the staff names, also if you click in the week day columns next to the staff names a drop down box should appear and all the the shifts are there to choose. Hope that makes sense

    Tiger

  8. #8
    Valued Forum Contributor wenqq3's Avatar
    Join Date
    04-01-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2003
    Posts
    868

    Re: Formula for male and female staff on rota

    I can see the list to choose staff name and the shift are follow behind. There is no drop down list below each weekday.
    How you create your drop down?

    http://www.officearticles.com/excel/...soft_excel.htm

    You can follow this one, and create for only staff name and shift time.

  9. #9
    Registered User
    Join Date
    07-18-2013
    Location
    Liverpool
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: Formula for male and female staff on rota

    Hi, I have already created the drop down box, screen print attached. It seems to only show up on Excel 2010

    Tiger Rota screen print.jpg

  10. #10
    Valued Forum Contributor wenqq3's Avatar
    Join Date
    04-01-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2003
    Posts
    868

    Re: Formula for male and female staff on rota

    Okay,obviously is not in my side. If you follow the steps that i send you. I confirm it will worked. As i already did in my site.
    You can send me again, if you can double check the list:D

  11. #11
    Registered User
    Join Date
    07-18-2013
    Location
    Liverpool
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: Formula for male and female staff on rota

    Hi, I have used named ranges and data validation which is in the tab named lists. The defined name for the staff drop down list is StaffNames

    Tiger

  12. #12
    Valued Forum Contributor wenqq3's Avatar
    Join Date
    04-01-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2003
    Posts
    868

    Re: Formula for male and female staff on rota

    Staff Schedule (EDIT2).xlsx

    I create with excel 2007.
    I name the range as TimeShift
    And create the list at C6
    If you with to apply to other cell. Just simply copy and paste to other cell.
    I think it will worked in your other pc with excel 2007:

  13. #13
    Registered User
    Join Date
    07-18-2013
    Location
    Liverpool
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: Formula for male and female staff on rota

    Hi, I have used named ranges and data validation which is in the tab named lists. The defined name for the staff drop down list is StaffNames

    Tiger

  14. #14
    Registered User
    Join Date
    07-18-2013
    Location
    Liverpool
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: Formula for male and female staff on rota

    Thank you very much for you help. I will try this when I go into work tonight and let you know, thanks again

    Tiger

  15. #15
    Valued Forum Contributor wenqq3's Avatar
    Join Date
    04-01-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2003
    Posts
    868

    Re: Formula for male and female staff on rota

    Hi bigtiger1,
    any issue regards your problem? If is solved, please mark as SOLVED.

  16. #16
    Registered User
    Join Date
    07-18-2013
    Location
    Liverpool
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: Formula for male and female staff on rota

    Hi wenqq3, it is now functioning on Excel 2007 at the office, thanks again for your help

    Tiger

  17. #17
    Valued Forum Contributor wenqq3's Avatar
    Join Date
    04-01-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2003
    Posts
    868

    Re: Formula for male and female staff on rota

    That's great

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Automatically adding Mr or Ms to male/female
    By TTej in forum Excel General
    Replies: 7
    Last Post: 10-03-2017, 11:49 AM
  2. Replies: 16
    Last Post: 01-16-2012, 07:09 AM
  3. Pivot Formula: Gender(male)/Gender(female)
    By peterso in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-04-2011, 04:23 PM
  4. Excel Male / Female Column
    By ringrim in forum Excel General
    Replies: 5
    Last Post: 08-13-2008, 05:28 PM
  5. IF male or female IF age is in range...THEN...
    By mtmagent in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-22-2006, 03:14 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1