Help! Checking a Box depending on matching information from other sheets

HollywoodStu

Hi there!

I'm hoping you can help me solve a problem which I'm having trouble getting my head around.

We have two sheets:

1 - School Invoices Sheets, with columns:

a) 'School Name' (Text column)

b) 'Type of Invoice' (with drop down options: '[1st] Pre-Delivery' and '[2nd] Delivery'

c) '[Check 3] Approved' (This is a check box)

2 - Region Sheet with columns:

a) 'School Name' (Text Column)

b) 'Pre-Delivery Invoice Approved' (check box)

c) 'Delivery Invoice Approved' (check box)

What I'd like to be able to do is have two formulas in the Region Sheet in both columns b and c. The two formulas would look in the Invoices Sheet, look for the corresponding School Name, check if the type of invoice is '[1st] Pre-Delivery' or '[2nd] Delivery' ,check if the Check 3 approved column is checked and then check the box in the Region sheet.

So, for e.g. Test School has an entry in the School Invoices sheet with a 1st Pre-Delivery option and the Check 3 approved column has been checked.

In the Region sheet, for the Test School row, the above variables all meeting requirements would appear as a checked box.

Is this possible? I'm hoping it is, but my formula work on Smartsheet is not the best!

Picture1.png

Picture2.png

Paul Newcome

You could use an INDEX/COLLECT like so:

=INDEX(COLLECT({Source Sheet Checkbox}, {Source Sheet School Name}, @cell = [School Name]@row, {Source Sheet Type}, @cell = "Pre-Delivery"), 1)

Paul Newcome

You could use an INDEX/COLLECT like so:

=INDEX(COLLECT({Source Sheet Checkbox}, {Source Sheet School Name}, @cell = [School Name]@row, {Source Sheet Type}, @cell = "Pre-Delivery"), 1)

HollywoodStu

@Paul Newcome I owe you a pint! Worked perfectly!

Thanks so much!

Paul Newcome

Happy to help. 👍️