If, Index & Collect Formula issues
I have an issue with my current If, Index, and Collect formula.
I have two sheets. One for data collection, and another for the Reviewer's Agency, Schedule, and Email address. I want to automate this process where if I have a TFD C shift, the Reviewers email address will be added into the data collection sheet. Here's my formula:
=IF(Agency@row = "TFD", INDEX(COLLECT({Reviewers Emails}, {Reviewers Schedule}, Schedule@row), 1), IF(Agency@row = "NWFD", INDEX(COLLECT({Reviewers Emails}, {Reviewers Schedule}, Schedule@row), 1), IF(Agency@row = "GRFD", INDEX(COLLECT({Reviewers Emails}, {Reviewers Schedule}, Schedule@row), 1))))
So I have a list of Reviewers:
If TFD C schedule, then Phil is the reviewer
If TFD B schedule, then Joe is the reviewer
If NWFD A, B or C schedule, Rachel is the reviewer
If GRFD B schedule, Mary is the reviewer
If GRFD C schedule, Tyrel is the reviewer
With my current formula, only the TFD reviewers are working, but not any of the NWFD or GRFD. The NWFD and GRFD Reviewers are just blank. Below is a screen shot of my reference sheet:
What am I doing wrong? Any assistance would be greatly appreciated, and thank you in advance!
Thanks,
Adrienne
Best Answer
-
Try an INDEX/COLLECT instead.
=INDEX(COLLECT({Contact Column}, {Agency Column}, @cell = Agency@row, {Schedule Column}, @cell = Schedule@row), 1)
Answers
-
Which sheet is your screenshot of and can you provide a screenshot of the other sheet in question?
-
Here's a screen shot with the non-essential columns hidden. I was using the testing column to write out my formula, but I ultimately want the formula to be in the Medical Reviewer column. Currently the Medical Reviewer Column is a drop-down that we select and then mark "Ready to Review" when we want that row sent to the reviewer. I just want to automate the Reviewer's email into Medical Reviewer column based on the Agency and schedule listed per row. The first screen shot was the sheet listing the reviewers and their assigned agency and shift.
-
The 2nd screen shot is the data entry sheet where we want the formula to be written into the Medical Reviewer column
-
Try an INDEX/COLLECT instead.
=INDEX(COLLECT({Contact Column}, {Agency Column}, @cell = Agency@row, {Schedule Column}, @cell = Schedule@row), 1)
-
@Paul Newcome that worked!!! Thank you!!!!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!