IF(AND) Statement in a Checkbox

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

Smartsheet Screen Shot - OC Capacity3.png

Find more posts tagged with

Formulas

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Match or Index
I am trying to pull a phone number and email from our master roster that is on another sheet in Smartsheet. I cannot figure out the formula. This is the formula I have tried. =INDEX([EMPLOYEE NAME]@row , MATCH({IMS Master Roster Range 2}), 0)
Workflow Generate Impact Report PDF in Impact Form Response Sheet has become invalid
Hi All, I keep getting this annoying error when I try to trigger a PDF generation. I initially set up to trigger the PDF generation to kick off once the last field the needs data is "not blank". That is when the error started. I modified the sheet and added a yes/no drop down to trigger the PDF generation when the value in…
INDEX COLLECT MIN
Desired result is to pull the Initiative Name where the Days to Due Date is the least (so, due the soonest); then to make an index for the Top 10 Initiatives Due Soonest Currently using this formula: =INDEX(COLLECT({Project Master - Initiative}, {Project Master - Days to Due}, MIN({Project Master - Days to Due})), 1) This…