Display 0 when cell contains certain letters

[barbara58554]

I'm working on an attendance form that has columns showing P, T, W, etc that indicate if the child was in school or why the child was absent. A couple columns over are 3 columns showing if the child was served breakfast, lunch and/or snack. I want the days the child was gone to automatically fill with a 0 in the breakfast, lunch, snack columns. I have been trying to figure this out most of the day and finally seek your assistance.

Hope this visual helps:

M

T

W

T

CHILD'S NAME

BREAKFAST

LUNCH

SNACK

B

L

S

B

L

S

B

L

S

A

P

P

P

I want this column to display 0 if A, T, OR W is in 1st column

I want this column to display 0 if A, T, OR W is in 1st column

I want this column to display 0 if A, T, OR W​ is in 1st column​

I want this column to display 0 if A, T, OR W is in 2ND column​

I want this column to display 0 if A, T, OR W is in 2ND column​

I want this column to display 0 if A, T, OR W is in 2ND column​

I want this column to display 0 if A, T, OR W is in 3RD column​

I want this column to display 0 if A, T, OR W is in 3RD column​

I want this column to display 0 if A, T, OR W is in 3RD column​

I want this column to display 0 if A, T, OR W is in 4TH column​

I want this column to display 0 if A, T, OR W is in 4TH column​

I want this column to display 0 if A, T, OR W is in 4TH column​

<tbody> </tbody>

 

[SQUIDD]

Hi Barbara

so, in column F2,
if A,T OR W is in A2, you want 0, but what if it is none of them, what should it be?

example below

=IF(OR(A3="A",A3="T",A3="W"),0,"WHAT")

Dave

 

[SQUIDD]

I would say these are the 4 formula you need to use in the cells you require the answers

=IF(OR(A2="A",A2="T",A2="W"),0,"WHAT")
=IF(OR(B2="A",B2="T",B2="W"),0,"WHAT")
=IF(OR(C2="A",C2="T",C2="W"),0,"WHAT")
=IF(OR(D2="A",D2="T",D2="W"),0,"WHAT")

 

[barbara58554]

The desired end result are for the days children are absent...those cells should show a zero for meals claimed or I would be satisfied if those cells would turn a different color. I process meal claims for USDA reimbursement after the teachers submit their electronic attendance form. This is a safeguard I would like to implement to make sure meals are not claimed on days children are absent. So if B2 has a code indicating the child was absent, I want the B/L/S column to remind the teacher to not claim any meals for that day.

I don't know how to get the B/L/S columns to indicate no meals when children are absent. I'm open to other ways as long as the end result gets the teachers attention to stop claiming meals on these days. I've been doing this for over 10 years and am running out of patience to deal with this issue every week.

Not sure what "what" should be. Ha!

For the children who are present (P), the teachers will enter a 0 or 1 under B/L/S to indicate which meals were served.

 

[SQUIDD]

Hi Barbara

So really the issue is teachers are claiming meals even if the child is Absent.
So basically if the teachers done it properly, you would not have a problem.
I assume they enter the absent details correctly?

If so, why dont we make the formual to check if cell is A,T,W and if so will be 0, but if not will be 1, this way the teachers would not have to enter the data at all?
try the below
=IF(OR(D2="A",D2="T",D2="W"),0,1)

is this more like what you want?

OR

We could make some VBA code behind a button, so when you click the button, the data is automatically entered either 0 or 1.

What do you think?

Dave

 

[SQUIDD]

sorry

=IF(OR(A2="A",A2="T",A2="W"),0,1)

for F2 I assume

 

[barbara58554]

The only concern is children arrive late and depart early some days. So some may come after breakfast and wouldn't be claimed for that meal and would be claimed for lunch and snack. Or the child may show up on time and leave early if they have a doctor's appointment or become ill. So I don't think it would work to have 1 automatically fill for days they are present. I had a brain storming session with some of my teaching staff today. They truly do try to get the attendance turned in accurately! They love the idea of cells turning a different color on the days children are absent as a way to draw attention to it. I have the formatting set for the 1st 4 columns to turn yellow with red, bold font if W, T, A is entered. The step that isn't working is getting the corresponding B/L/S columns to turn yellow.

This is the formula I have: =IF(OR(A9="A",A9="T",A9="W"),0,1) and not all 3 cells turn yellow.

I tossed the form onto one of our web pages. It's at the top right under the icons for our social media. Would it help to see what I have done? https://www.hitinc.org/services/WestRiverHeadStart/ResourceLinks/

 

[Eric W]

You could use Conditional Formatting to do it. For example:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	M	T	W	Th		Monday			Tuesday			Wednesday			Thursday
2					Child's Name	Breakfast	Lunch	Snack	B	L	S	B	L	S	B	L	S
3	A	A	P	P	Ann
4	W	P	P	A	Bob
5	A	T	W	A	Cathy
6	P	W	P	P	Don
7	P	p	P	p	Elayne
8
9

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

To accomplish this, select columns F:Q by clicking on the letters across the top. Then Click Conditional Formatting > New Rule > Use a Formula > and enter:

=AND(ROW()>2,FIND(INDEX($A$1:$D$100,ROW(),(COLUMN()-6)/3+1),"ATW"),$E1<>"")

Change the bottom row (100) to be larger than your actual sheet.Then click Format... and select a fill color.

This formula is highly dependent on the columns you use. If you use different columns (I couldn't reference your spreadsheet), let me know and I'll adjust it.

Hope this helps!

 

[SQUIDD]

Hi Barbara

Ok, so if i understand correctly, you would like the columns to change colour if A,T or W is in the 1st column.
Are familiar with conditional formatting?

On the home tab of excel, you should have "conditional formatting"

You will need to select the 1st cell you want the colour to change on(so you can copy down the formula within th column)
Then click the conditional formatting
click new rule
next click use a formula to determine which cells to format
then enter the formula

=IF(or($A1="A",$A1="T",A1="W"),TRUE,FALSE)

CHANGE $A1 TO THE YOUR 1ST CELL
Next select the format
then click on fill
next choose the colour you want the cell to go if the formula is true.
then click ok
then click ok again
now copy the cells down like you would an ordinary formula

Then simply replicate the above sequence on your other columns.

Dave

 

[Eric W]

I managed to get to your spreadsheet after all. Here's how I adapted the formula to your sheet:

Select the range F9:Q28. Click Conditional Formatting > New Rule > Use a formula > and enter:

=SEARCH(OFFSET($A9,0,(COLUMN()-6)/3),"ATW")*($E9<>"")

Choose your fill color. Let us know if this works.