• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Show value as per importance

Tom22

Member
Hi,

I am working on a one file, where project status has to be maintained.

Status are Delayed, Risk of Delayed and on track.

File has 2 tabs, first tab is the place where we want to show value from second tab.it is kind of a summary tab

Second tab where we have details about any project.

Point to note- one project is divided into different phases.one project can have N number of phases.

One phase status can be different from others.

in 1st tab we have this one project name only. Formula need to read each phase status from 2nd tab and then show the status on first tab as per importance.

Importance is as follows:-
    • If the status shows DELAY on any of the phases for that project, the formula for tab1 shows “DELAY”; if not, then:
    • If the status shows RISK OF DELAY on any of the phases for that project, the formula for tab1 shows “RISK OF DELAY”: if not, then:
    • Formula shows “ON TRACK
File attached as sample
 

Attachments

p45cal

Well-Known Member
In cell J4:
Code:
=INDEX({"DELAY","RISK OF DELAY","ON TRACK"},MATCH(TRUE,ISNUMBER(MATCH({"DELAY","RISK OF DELAY","ON TRACK"},(IF('2nd Tab'!$B$2:$B$21=$C4,'2nd Tab'!$N$2:$N$21)),0)),0))
which, depending on your version of Excel, may need to be entered with Ctrl+Shift+Enter, rather than the more usual plain Enter.
Then copy down.
You can enclose the whole formula in an IFERROR function to hide the error when a project isn't found or when none of the Statuses exist.
It the first formula I got to work so there's probably a much more elegant one.
 
Top