Scheduling Help?

[Kib]

Hey, This is my first post, and i have taken a look around the forums and haven't found anything that quite meets what i need, and i cant seem to work up my own formula to do this.
But basically I'm creating a military leave schedule for my unit. What i need it to do is when i have more than 4 people on leave, to highlight the 5th members leave date to notify me that we have exceeded the number of people allowed on leave at one time. I want to use "L" as the symbol for leave. I have manually highlighted an example of what i want to do and attached it. Any help would be appreciated
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]L
[/TD]
[TD]L
[/TD]
[TD]L
[/TD]
[TD]L
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]L
[/TD]
[TD]L
[/TD]
[TD]L
[/TD]
[TD]L
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]L
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]L
[/TD]
[TD]L
[/TD]
[TD]L
[/TD]
[TD]L
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]L
[/TD]
[TD]L
[/TD]
[TD]L
[/TD]
[TD]L
[/TD]
[TD]L
[/TD]
[TD]L
[/TD]
[TD]L
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]L
[/TD]
[TD]L
[/TD]
[TD]L
[/TD]
[TD]L
[/TD]
[TD]L
[/TD]
[TD]L
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]L
[/TD]
[TD]L
[/TD]
[TD]L
[/TD]
[TD]L
[/TD]
[TD]L
[/TD]
[TD]L
[/TD]
[TD]L
[/TD]
[TD]L
[/TD]
[TD]L
[/TD]
[TD]L
[/TD]
[/TR]
[TR]
[TD]L
[/TD]
[TD]L
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]L
[/TD]
[TD]L
[/TD]
[TD]L
[/TD]
[TD]L
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Bairn90]

Something like this should do.

Set conditional formatting to highlight based on this formula assuming your range starts in column A. Set the range the formatting applies to and this will change to match each column. It will then highlight any L's that exceed the limit of 4

=IF(A1>"",COUNTIF(A$1:A1,"L")>4)

Any questions let me know.

 

[Kib]

It doesn't seem to work with the spreed sheet I am using? I placed the formula into all the cells in column A then placed "L" into 4 cells (A1,A4,A6,A8) then typed "L" into A10. Did i do something wrong, or do i need to adjust the formula?

 

[Bairn90]

Hi Kib,

The formula doesn't go into any cells it is placed into conditional formatting. Which version of excel are you using? Where to find the conditional formatting option varies depending on version.

If you can't find it let me know and I'll tell you where to find it in your version.

Essentially what you need to do is:

1. Highlight the columns you want to highlight the extra L's
2. From the conditional formatting menu, select highlight cells rules
3. Pick the more rules right at the bottom of the list
4. At the bottom of the box that appears select "Use a formula to determine which cells to format"
5. Paste in the formula into the text box and select your formatting to turn text red
6. Click ok

Now any L in the columns you highlighted over 4 will be formatted to the format you chose.

 

[Kib]

Thanks! that worked perfectly, helped with some brownie points with my sergeant too lol.