Index/Match on Checkbox
I have two sheets,
1- Stakeholder matrix with contact information and checkbox for primary contact
2- Project plan
I need to pull the email address's from Sheet 1 that have been "checked" as primary into sheet 2
Answers
-
Are you able to provide screenshots (sample data if necessary) for context?
-
Yes.
Sheet 1 - Stakeholder Engagement Matrix- If the Box in the first column is checked, I want to move the email address to sheet 2.
Sheet 2- Email should go in column labeled Client Stakeholders
-
We can help with a formula to pull the data over, but without knowing any criteria other than the checkbox it is hard to say exactly. I'll provide a formula and explain it, and if you need something else please let us know.
- Create this formula in Client Stakeholders =IF({Sign Off Document 1}=1,{Email 1})
So this formula uses an inter-sheet reference to check and see if the Sign Off Document field from Sheet 1 is checked ("=1") and if so uses a second reference to bring the email over.
I would guess though that you need some other reference (Project name or number) to correlate Sheet 2 to the entry on Sheet 1. If that is the case, you could easily create a column formula that does this. That formula (built assuming you want to use the Project Name) would look like this:
- =IFERROR(IF(INDEX({Sign Off Document Range}, MATCH([Project]@row, {Project Range}, 0))=1, INDEX({Email Range}, MATCH([Project]@row, {Project Range}, 0))),"")
This would go in Client Stakeholders on Sheet 2 as a column formula and you would need to create the references from Sheet 1.
Hope this helps!
Best,
Zach Hall
Training Delivery Manager / Charter Communications
-
How do we know which row to put each email address in?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!