+ Reply to Thread
Results 1 to 13 of 13

Hello! Rostering problem to prevent over 19 consecutive days without a break. =/

  1. 11-04-2015, 09:48 PM #1
    wcngu1
    wcngu1 is offline
    Registered User
    Join Date
    12-16-2013
    Location
    Tokyo
    MS-Off Ver
    Excel 2007
    Posts
    42

    Hello! Rostering problem to prevent over 19 consecutive days without a break. =/

    Hi peoples,

    I was wondering if its possible for a row to have conditions of preventing an employee from being rostered for over 19 consecutive days. Maybe when they are trying to go over 19 an alert pops up to reject the input?

    In the excel roster we have there are different symbols that indicate a break in consecutive days. this can be annual leave/weekends/rdo's etc.

    Is it possible to put a condition where the breaks can represent multiple symbols as breakers?



    e.g. in my roster these are the breakers:

    - R/O
    - DIL
    - ADO
    - LI
    - RL
    - PH
    - SD
    - NA
    - FLEX

    I have attached an example of the roster I'm trying to implement it to.

    Usually I have a crack at a problem myself but with this one I have no idea at all what function to use. this is totally new territory for me haha
    any help would be great! thanks everyone =)
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 11-04-2015, 11:21 PM #2
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,939

    Re: Hello! Rostering problem to prevent over 19 consecutive days without a break. =/

    Seeing as how you have all those colors already, I would nt use Conditional Formatting. But perhaps Data Validation with something like this as a rule...
    =SUM(COUNTIF(C6:U6,"<>"&{"R/O","DIL","ADO","LI","RL","PH","SD","NA","FLEX"}))>19
    You would start this from column 19
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  3. 11-04-2015, 11:53 PM #3
    wcngu1
    wcngu1 is offline
    Registered User
    Join Date
    12-16-2013
    Location
    Tokyo
    MS-Off Ver
    Excel 2007
    Posts
    42

    Re: Hello! Rostering problem to prevent over 19 consecutive days without a break. =/

    Hi FDibbins!

    thanks for your help =)

    I had a crack and put your code into the data validation under "custom" but got some crazy jargon back as an error "you may not use unions, intersections or array constants for data validation criteria". This made my head explode haha.

    Did I put this in the wrong area?

    Sorry for being a pain!

    Cheers
    Reply With QuoteRegister To Reply
  4. 11-05-2015, 01:55 AM #4
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,939

    Re: Hello! Rostering problem to prevent over 19 consecutive days without a break. =/

    You are correct, I wasnt thinking - DV and CF wont accept that kind of formula

    I am going to push this out to the other experts, I have a feeling you will need VBA for this
    Reply With QuoteRegister To Reply
  5. 11-05-2015, 02:08 AM #5
    wcngu1
    wcngu1 is offline
    Registered User
    Join Date
    12-16-2013
    Location
    Tokyo
    MS-Off Ver
    Excel 2007
    Posts
    42

    Re: Hello! Rostering problem to prevent over 19 consecutive days without a break. =/

    all good FDibbins =) your help is appreciated either way haha

    maybe it isnt possible in excel then? hmmm
    Reply With QuoteRegister To Reply
  6. 11-05-2015, 03:54 AM #6
    :) Sixthsense :)
    :) Sixthsense :) is offline
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: Hello! Rostering problem to prevent over 19 consecutive days without a break. =/

    Keep a Helper sheet with the below data

    A
    1
    R/O
    2
    DIL
    3
    ADO
    4
    LI
    5
    RL
    6
    PH
    7
    SD
    8
    NA
    9
    FLEX


    Use the below formula in your DV or CF

    =SUMPRODUCT(COUNTIF(C6:U6,"<>"&Helper!$A$1:$A$9))>19


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved

    If the suggestion helps you, then Click *below to Add Reputation
    Reply With QuoteRegister To Reply
  7. 11-05-2015, 03:05 PM #7
    xladept
    xladept is offline
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Hello! Rostering problem to prevent over 19 consecutive days without a break. =/

    Here's an Event Procedure for your Sheet1 module:

    Please Login or Register  to view this content.
    And here's your book with the procedure:
    Attached Files Attached Files
    Last edited by xladept; 11-05-2015 at 05:42 PM. Reason: change to code in book
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
    Reply With QuoteRegister To Reply
  8. 11-09-2015, 12:53 AM #8
    wcngu1
    wcngu1 is offline
    Registered User
    Join Date
    12-16-2013
    Location
    Tokyo
    MS-Off Ver
    Excel 2007
    Posts
    42

    Re: Hello! Rostering problem to prevent over 19 consecutive days without a break. =/

    Quote Originally Posted by :) Sixthsense :) View Post
    Keep a Helper sheet with the below data


    Use the below formula in your DV or CF

    =SUMPRODUCT(COUNTIF(C6:U6,"<>"&Helper!$A$1:$A$9))>19
    thanks for the suggestion Sixsense! Unfortunately both DV and CF said I can't use another sheet as a reference? did it work for you?

    Cheers
    Reply With QuoteRegister To Reply
  9. 11-09-2015, 12:54 AM #9
    wcngu1
    wcngu1 is offline
    Registered User
    Join Date
    12-16-2013
    Location
    Tokyo
    MS-Off Ver
    Excel 2007
    Posts
    42

    Re: Hello! Rostering problem to prevent over 19 consecutive days without a break. =/

    Quote Originally Posted by xladept View Post
    Here's an Event Procedure for your Sheet1 module:
    This worked perfect xladept! =) thanks for that. I guess vba is the only way to go? I'm always fascinated when people solve complex problems with vba it looks so crazy.

    Thanks again!
    Reply With QuoteRegister To Reply
  10. 11-09-2015, 04:06 PM #10
    xladept
    xladept is offline
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Hello! Rostering problem to prevent over 19 consecutive days without a break. =/

    You're welcome and thanks for the rep!

    It's amazing what you can do if you know a little VBA
    Reply With QuoteRegister To Reply
  11. 11-09-2015, 04:10 PM #11
    xladept
    xladept is offline
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Hello! Rostering problem to prevent over 19 consecutive days without a break. =/

    You're welcome and thanks for the rep!

    It's amazing what you can do if you know a little VBA
    Reply With QuoteRegister To Reply
  12. 11-12-2015, 01:16 AM #12
    :) Sixthsense :)
    :) Sixthsense :) is offline
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: Hello! Rostering problem to prevent over 19 consecutive days without a break. =/

    Quote Originally Posted by wcngu1 View Post
    Unfortunately both DV and CF said I can't use another sheet as a reference?
    Yes, it will work if your excel version is 2010 or greater.

    Upto 2007 we have to pass the next sheet reference in a named range and use that named range name in DV or CF formula.

    Refer the below links to know how to do it

    http://www.ion.icaew.com/itcounts/po...een-the-sheets

    http://blog.contextures.com/archives...rent-workbook/

    http://blog.contextures.com/archives...fferent-sheet/
    Reply With QuoteRegister To Reply
  13. 11-22-2015, 07:54 PM #13
    wcngu1
    wcngu1 is offline
    Registered User
    Join Date
    12-16-2013
    Location
    Tokyo
    MS-Off Ver
    Excel 2007
    Posts
    42

    Re: Hello! Rostering problem to prevent over 19 consecutive days without a break. =/

    Quote Originally Posted by :) Sixthsense :) View Post
    Yes, it will work if your excel version is 2010 or greater.
    It all makes sense now! we only have excel 2007 right now =( but i'll keep your as a reference when we finally upgrade! thanks again!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Count Consecutive Days while bridging over days off and holidays
    By chad479 in forum Excel General
    Replies: 17
    Last Post: 08-03-2015, 06:15 AM
  2. [SOLVED] Adding Consecutive days (with a date from & to)
    By learnerdriver in forum Excel General
    Replies: 4
    Last Post: 04-01-2015, 11:48 PM
  3. Prevent Page Break in blocks of data
    By installtechireland in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-06-2014, 09:19 AM
  4. [SOLVED] How to count 10 consecutive week days
    By rjassal82 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-16-2013, 05:40 AM
  5. Employee Time Tracking - Sick days & Consecutive Days
    By notaguru6 in forum Excel General
    Replies: 6
    Last Post: 08-09-2013, 12:50 PM
  6. Prevent Consecutive Entries
    By JorgeAE in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-28-2006, 12:00 PM
  7. Rostering problem
    By Hartygan in forum Excel General
    Replies: 1
    Last Post: 08-26-2005, 12:13 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1