Cross reference logic help needed

I need to be able to check if a set of conditions is met across different rows in another sheet. I need to determine if supporting projects should be yes if supporting helper column is yes in the other sheet. If any of the rows for a project resource has a yes in supporting helper column that resource should have a yes in supporting project in the other sheet.
Best Answer
-
Hello @Jess A ,
I replaced the VLOOKUP with IF logic embedded with HAS and Cross Sheet Reference.
It worked for me.=IF(AND(HAS({Project Hours Question Project Resource}, Resource@row), HAS({Project Hours Question Project Resource Supporting Helper Column}, "Yes")), "Yes", "No")
Scenario 1 ⇒ Jane Doe last Record is YesErin Horiuchi Green, MBA, LSSYB, PSMI
Process Manager
Syneos Health
Please kindly like ❤️, upvote ⬆️ and/or mark ✅ any of my contributions that have provided value.
Core App and Project Managment Certified 🚀
Answers
-
Hello @Jess A ,
Please let me know if I misunderstood your question.
I interlaced the functions IF, VLOOKUP and embedded a Cross Sheet Reference.
Cross Sheet Reference = {Project Resource} = Column Range=IF(VLOOKUP(Resource@row, {Project Resource}, 5, 0) = "Yes", "Yes", "No")
Erin Horiuchi Green, MBA, LSSYB, PSMI
Process Manager
Syneos Health
Please kindly like ❤️, upvote ⬆️ and/or mark ✅ any of my contributions that have provided value.
Core App and Project Managment Certified 🚀 -
I don't think a straight vlookup would work. In the case of Jane Doe there are three rows for that resource. 2 are yes and 1 is no, I need to be able to look at all three rows and if one of them is yes then return yes.
-
Hello @Jess A ,
Fair point. Thank you for the additional requirement clarification.
Let me give this another go using other functionality. Please stay tuned.Erin Horiuchi Green, MBA, LSSYB, PSMI
Process Manager
Syneos Health
Please kindly like ❤️, upvote ⬆️ and/or mark ✅ any of my contributions that have provided value.
Core App and Project Managment Certified 🚀 -
@Gillian C any suggestions?
-
Hello @Jess A ,
I replaced the VLOOKUP with IF logic embedded with HAS and Cross Sheet Reference.
It worked for me.=IF(AND(HAS({Project Hours Question Project Resource}, Resource@row), HAS({Project Hours Question Project Resource Supporting Helper Column}, "Yes")), "Yes", "No")
Scenario 1 ⇒ Jane Doe last Record is YesErin Horiuchi Green, MBA, LSSYB, PSMI
Process Manager
Syneos Health
Please kindly like ❤️, upvote ⬆️ and/or mark ✅ any of my contributions that have provided value.
Core App and Project Managment Certified 🚀 -
Thank you! I will try it with my actual data!
-
@Jess A sorry! was offline. Looks like great help from @Erin Horiuchi Green 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.5K Get Help
- 433 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 505 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!