Unable to get formulas to work

Andrée Starå

@PJay128 Here you go!

This is for the Elevator status.

=IF(AND(FIND(Site@row, JOIN(COLLECT({Heath & Safety Audits Range 1}, 
{Heath & Safety Audits Range 2}, AND(IFERROR(YEAR(@cell), 0) = 2019)), ".")) > 0, 
FIND(Site@row, JOIN(COLLECT({Heath & Safety Audits Range 1}, 
{Heath & Safety Audits Range 3}, (@cell) = "Elevator"), ".")) > 0), "Yes", "No")

Did it work?

Paul Newcome

@Andrée Starå

Couldn't you just include the year and "Elevator" status in the same JOIN/COLLECT?

=IF(FIND(Site@row, JOIN(COLLECT({Heath & Safety Audits Range 1}, 
{Heath & Safety Audits Range 2}, IFERROR(YEAR(@cell), 0) = 2019, 
{Heath & Safety Audits Range 3}, (@cell) = "Elevator"), ".")) > 0, 
"Yes", "No")

Andrée Starå

@Paul Newcome Yes, you're correct. I missed that it was the same pattern.

PJay128

Lovely works a treat Paul.

Thankyou to both of you :)

Merry Christmas

Andrée Starå

@PJay128

Excellent!

It was my pleasure!

Happy Holidays!

Paul Newcome

Happy to help! 👍️