Cross reference logic help needed

Jess A

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.

Erin Horiuchi Green

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 Yes

Erin Horiuchi Green

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")

Jess A

Hi @Erin Horiuchi Green,

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.

Erin Horiuchi Green

Hello @Jess A ,

Fair point. Thank you for the additional requirement clarification.

Let me give this another go using other functionality. Please stay tuned.

Jess A

@Gillian C any suggestions?

Erin Horiuchi Green

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 Yes

Jess A

Thank you! I will try it with my actual data!

Gillian C

@Jess A sorry! was offline. Looks like great help from @Erin Horiuchi Green 👍️

Jess A

=IF(AND(HAS({Project Hours Question Project Resource}, Resource@row), HAS({Project Hours Question Project Resource Supporting Helper Column}, "Yes")), "Yes", "No")

How do I do the cross reference piece?

When I do it I'm getting all yes.