IF(AND) Statement in a Checkbox

Sean Taber

Hello,

I've been working on this for an hour or so now and I keep getting an unparseable  or invalid error message.  I want all applicable check boxes to automatically check off when a person is equal to or greater than a 3 rating.  I need to do this for 48 people across 14 different work types.  It worked when I clicked on the cells but because there are a lot of moving pieces I wanted to make the formula as high level as possible, in case any edits are needed.   The IF(AND) statement also has to reference data from another sheet because the data is sensitive.  Right now my formula reads:  =IF(AND({Employee Name}@row=[Employee Name]1, {Standard Billing}>=3),1,0).  Employee Name and Standard Billing are both reference names for the columns in the other sheet.  This gave me an unparseable message.  

I attached the grid that has the check boxes.  The grid I am referencing has numbers where the check boxes are and this information can only be visible to managers.  The grid with the check boxes will be visible to associates so they can assign work to the folks in the list.

Ultimately I just want the box to be checked if the persons name is in the employee name column and they have a 3 or above rating in any of the work types (communicator, startup, standard billing, advanced billing, hospital billing etc) check the box.

Thank you,

Sean

J. Craig Williams

You are barking up the wrong tree

=IF(AND({Employee Name}@row=[Employee Name]1, {Standard Billing}>=3),1,0)

I assume both {Employee Name} and {Standard Billing} are references to a full column on the other sheet.

If so then {Employee Name}@row is not valid syntax. Even if it was, what you looking for is either the VLOOKUP() function or the INDEX(...,MATCH()) pair.

MATCH on [Employee Name]@row (on the current sheet) with the {Employee Name} on the other sheet. It looks like your data is likely unsorted so use 0 for the last argument. This returns the row in other sheet that matches the name.

=INDEX({Standard Billing}, MATCH(...)) >= 3

(or use IF(INDEX(....)>=3,1,0) if you prefer)

should do it. I haven't filled in all the details, because I don't have time to test adn don't want to imply the answer is as-written.

Craig

Sean Taber

You are correct.  I am referencing columns.  Ok, I'm switching gears and are now looking to use Red, Yellow, Green symbols instead of a check box.  Can I use the same formula to say if X Person is >=3 "Green", If X Person = 2 "Yellow", Red?  

J. Craig Williams

Of course, but you'll need to use the IF statement instead of the just the formula.

Nested IF's with long expressions to check can get ugly.

=IF( ****your formula to get the value**** >= 3, "Green", IF (****your formula to get the value**** = 2, "YELLOW", "RED")

and you may decide to use RYGB and then have to modify it again.

But that (Nested IF) is going to be the common answer and is good enough for now.

Craig

Sean Taber

I hadn't seen a response.  Does anyone know why my nested if statement below is not working consistently?  I created the nested If statement and it is messy.  It works but not all the time.  Here it is:  =IF(INDEX({Standard Billing}, MATCH([Employee Name]2, {OCA Name})) >= 3, "Green", IF(INDEX({Standard Billing}, MATCH([Employee Name]2, {OCA Name})) = 2, "Yellow", "Red"))

This is not consistent.  It returns Green when should be Red and Red when it should be Green or Yellow.  

Sean