This is about Sundays attendance for our members. This is what I want to
accomplish. If a member is absent for 3 sundays consecutively (e.g on
C5=absent, D5=absent and E5=absent, then it will tell me on B5 as "Needs
Follow up". The same thing goes on other columns up to 300. Thanks

formula for B5 - then copy formula down the column

= if(countif(C5:E5, "absent")=3,"Needs Follow up","")

"Boogie" wrote:

This is about Sundays attendance for our members. This is what I want to
accomplish. If a member is absent for 3 sundays consecutively (e.g on
C5=absent, D5=absent and E5=absent, then it will tell me on B5 as "Needs
Follow up". The same thing goes on other columns up to 300. Thanks

how about if the 3 consecutive happened on x=100 y=100 z=100 or
f=20,g=20,h=20. I need something dynamic as long as the absences happened on
3 consecutive cells. Thanks

"Vacation's Over" wrote:

formula for B5 - then copy formula down the column

= if(countif(C5:E5, "absent")=3,"Needs Follow up","")

"Boogie" wrote:

This is about Sundays attendance for our members. This is what I want to
accomplish. If a member is absent for 3 sundays consecutively (e.g on
C5=absent, D5=absent and E5=absent, then it will tell me on B5 as "Needs
Follow up". The same thing goes on other columns up to 300. Thanks

be specific:
1)are you saying not to look for the word "absent"
2)or are you saying to search a row and see if the word "absent" is ever in
3 consecutive columns
3) or are you looking tosearch a row and see if any value is every in 3
consecutive rows?

"Boogie" wrote:

This is about Sundays attendance for our members. This is what I want to
accomplish. If a member is absent for 3 sundays consecutively (e.g on
C5=absent, D5=absent and E5=absent, then it will tell me on B5 as "Needs
Follow up". The same thing goes on other columns up to 300. Thanks

Example:
A B C D E F G
H I J .......
1 Names Status
2 Charles A A A
3 Peter
4 Sam
A A A
5 George A A A

On the above example, I B2 should be "Absent" since Charles has 3
consecutive absent. I also want B4 be "Absent" since Sam has 3 consecutive
absent. Of course not for George since his absences are not consecutive.
Please assume that I have 300 members. Thanks and I hope this is clear now.
God bless.


"Vacation's Over" wrote:

be specific:
1)are you saying not to look for the word "absent"
2)or are you saying to search a row and see if the word "absent" is ever in
3 consecutive columns
3) or are you looking tosearch a row and see if any value is every in 3
consecutive rows?

"Boogie" wrote:

This is about Sundays attendance for our members. This is what I want to
accomplish. If a member is absent for 3 sundays consecutively (e.g on
C5=absent, D5=absent and E5=absent, then it will tell me on B5 as "Needs
Follow up". The same thing goes on other columns up to 300. Thanks

Oops. sorry, the actual posting went garbled. Letter are column Letters and
numbers are Row numbers.

"Vacation's Over" wrote:

be specific:
1)are you saying not to look for the word "absent"
2)or are you saying to search a row and see if the word "absent" is ever in
3 consecutive columns
3) or are you looking tosearch a row and see if any value is every in 3
consecutive rows?

"Boogie" wrote:

This is about Sundays attendance for our members. This is what I want to
accomplish. If a member is absent for 3 sundays consecutively (e.g on
C5=absent, D5=absent and E5=absent, then it will tell me on B5 as "Needs
Follow up". The same thing goes on other columns up to 300. Thanks

this could be done with macros / VBA but you seem more comfortable with
formulas so let's try :

You can get the third consecutive absence to format red background
for cell E4 (first cell with 2 columns of potential absences to left
using"A" for absence as in your post

Select E4 through last row used and last column used then:

formatConditional Fromat
Formula is
=And(E4+"A",D4="A",C4="A")

formatpatterns red background


This is fast and easy and you may be able to adapt for other things

"Boogie" wrote:

Oops. sorry, the actual posting went garbled. Letter are column Letters and
numbers are Row numbers.

"Vacation's Over" wrote:

be specific:
1)are you saying not to look for the word "absent"
2)or are you saying to search a row and see if the word "absent" is ever in
3 consecutive columns
3) or are you looking tosearch a row and see if any value is every in 3
consecutive rows?

"Boogie" wrote:

This is about Sundays attendance for our members. This is what I want to
accomplish. If a member is absent for 3 sundays consecutively (e.g on
C5=absent, D5=absent and E5=absent, then it will tell me on B5 as "Needs
Follow up". The same thing goes on other columns up to 300. Thanks