Cross sheet Index & match with a filter to only pull through data that matches certain values

Archie

I've got a master tracker for our project- area by area, and then another sheet which gives a breakdown for drawings for each area and their approval status

for example - one area could have 3 or 4 drawings related to it. Each drawing has to be approved before we can start work on that area.

I want to create a formula that will look up the drawing tracker for "Area A" (from master tracker), if all drawings are "Approved" then update my Master tracker to show 'Approved'. If all drawings for that area have not been approved then the Master tracker is left blank or say 'not approved'

Drwg Tracker.png

Main Sheet.png

I'm currently using the below formula but this just pulls through the first result that matches

=INDEX({Drawing Tracker Range 1}, MATCH(Location@row, {Drawing Tracker Range 2}, 0))

Paul Newcome

Try a COUNTIFS to see if any are not "Approved". Then drop it into an IF that says if that count is greater than zero, "Not Approved", otherwise (meaning there are none that aren't approved) "Approved".

=IF(COUNTIFS({Source Sheet Location Column}, @cell = Location@row, {Source Sheet Approval Column}, @cell <> "Approved")> 0, "Not Approved", "Approved)

Paul Newcome

Try a COUNTIFS to see if any are not "Approved". Then drop it into an IF that says if that count is greater than zero, "Not Approved", otherwise (meaning there are none that aren't approved) "Approved".

=IF(COUNTIFS({Source Sheet Location Column}, @cell = Location@row, {Source Sheet Approval Column}, @cell <> "Approved")> 0, "Not Approved", "Approved)

Archie

thanks that's sorted it!

Paul Newcome

Happy to help. 👍️