Help! Checking a Box depending on matching information from other sheets
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!
Best Answer
-
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)
Answers
-
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)
-
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!