+ Reply to Thread
Results 1 to 5 of 5

Rolling Count Reset to Zero (dependant on txt not numbers)

  1. #1
    Registered User
    Join Date
    10-13-2011
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2007
    Posts
    7

    Rolling Count Reset to Zero (dependant on txt not numbers)

    Hi

    I am looking for a code or formula that will allow me to count how many conditionally formatted cells are in a row and colour them accordingly, then reset to "0" when a particular word/colour is selected. Cells contain txt not numbers:
    A1:A14
    A1:A5 contain combination of either "Day", "Night", "Swing"(which are conditionally formatted to turn green)
    First part is:
    If these words/colours are consecutive in 1-6 cells they should remain green.
    If these words/colours are consecutive from 1-10 cells then the first lot 1-6 should remain green, but the second lot 7-10 should turn orange.
    If these words/colours are consecutive from 1-14 cells then the first lot 1-6 should remain green, the second lot 7-10 should turn orange and the last lot 11-14 should turn red. (we call this traffic light reporting as this gives an indication of when things are from good to caution to alert).

    Second part is:
    If the word "Rest" (which is set to be non coloured cell) then I would need the count to be reset to "0" and the process start from the next cell depending on the word/colour.

    I hope I have made sense - any assistance would be much appreciated.

    Kind regards
    Sabrina78
    Last edited by Sabrina78; 08-06-2012 at 08:00 PM. Reason: amendment to title

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Rolling Count Reset to Zero (dependant on txt not numbers)

    Hi

    Is it possible to have a "helper" column that can be used to assist?

    rylo

  3. #3
    Registered User
    Join Date
    10-13-2011
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Rolling Count Reset to Zero (dependant on txt not numbers)

    yes rylo can incorporate helper column which I'm trying on at the moment - but just cannot figure it out!

    thanks
    sabrina78

  4. #4
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Rolling Count Reset to Zero (dependant on txt not numbers)

    Hi

    Data in range A1:A14
    B1: 1
    B2: =IF(A2="Rest",0,B1+1) Copy down to B14

    Highlight range A1:A14
    Conditional Formatting, Use formula and insert the following 3 rules
    =AND(B1>0,B1<=6) with a green fill
    =AND(B1>0,B1<=10) with orange fill
    =AND(B1>0,B1<=14) with red fill

    Note the conditional formatting rules have to be in that order.

    See how that goes

    rylo

  5. #5
    Registered User
    Join Date
    10-13-2011
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Rolling Count Reset to Zero (dependant on txt not numbers)

    thanks Rylo

    but I can't seem to get it to work - is this coz your formula is reading columns rather that row?

    Sorry just realised that I stated columns - but need it: A1:L1

    cheers
    sabrina78
    Last edited by Sabrina78; 09-06-2012 at 11:30 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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