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
-
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)
Answers
-
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)
-
thanks that's sorted it!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66K Get Help
- 429 Global Discussions
- 150 Industry Talk
- 488 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 152 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!