Formula for a checkbox when 2 criteria are met

Michelle Lien

Hello, I've been trying to figure out how to do this and need some help. I have two sheets:

I want to make the checkbox true when:

1. case ID FF matches a case ID SE
2. AND if the caseID SE has the date column filled in (anything except blank)
   1. if the date column is a blank, then the checkbox should be unchecked

So far I can generate a formula asking to search for a matching caseID between both sheets.

=IF(COUNTIF({Case ID SE}, [Case ID FF]@row) > 0, 1, 0)

I tried adding on an AND to the end of the formula but it returns as unparseable.

Any help is appreciated, thanks!

Mike Wilday

If you want to use an And statement then you'll need to add it before the first statement and separate it by a comma like this.

IF(AND(logical statement 1, Logical statement 2), "THEN THIS", "Else THis")

Does that make sense?

https://help.smartsheet.com/function/and

Michelle Lien

https://community.smartsheet.com/discussion/comment/268999#Comment_268999

I'm not sure how to apply that to what I need the formula to do.

I need it to find matching rows between case ID FF and case ID SE. Then only be checking the box if the matching case have a date in the other column in the SE sheet.

Mike Wilday

I think the way I would tackle it is to do a vlookup in the FF sheet in a new column and pull over the SE date if one exists. You could store the SE date on the FF sheet. Then you could just do the checkbox automation based on if the date is populated on the FF sheet - which would be referencing the SE sheet. Does that make sense?

Here's how to write a vlookup function:

https://help.smartsheet.com/function/vlookup

Stacy Meadows

@Mike Wilday Have you got an example of your explanation? as I am trying to move data from one sheet to another if a box is ticked however getting no where....

your help and time would be appreciated :)

Mike Wilday

You can collect data from another sheet (or within the same sheet) using vlookup.

Say you have a vendor column in your primary sheet and you want to pull their phone number from a vendor info sheet into your primary sheet. The Vendor Info sheet has the following columns Vendor Name, Address, Phone Number columns.

Use = Vlookup(Vendor@row, [insert cross sheet reference], 3, false) in a new column on your sheet.

That would search for whatever is in the vendor column at that row in your primary sheet and match it to your vendor info sheet, and then it would pull column 3 (your phone number) and return it in your formula.

To initiate a cross-sheet reference for after the vendor@row part, you would click on Reference another sheet as indicated below.

https://help.smartsheet.com/function/vlookup