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

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'

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

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!