If statement to auto link to different cells

Lucas Knepper

On our PM sheet we have stage summaries which show the % complete, start/end date, and a RYG dot. All of this information appears on a dashboard. Currently when we change stages we have to re-link all of the above info to the dashboard but is there a way we can have a formula that automatically links to the corresponding stage.

i.e. (referencing the attached picture) Stage 1 is now complete so when we click that we are now moving on stage 2 can we have an IF Formula that will automatically show Stage 2 % complete, start/end date, and its RYG dot on our project dashboard? 

 

 

Shaine Greenwood

Hi Lucas,

You might consider adding all of the stage data to your metrics widgets and then including a "current stage" rich text widget so people will know what stage is currently important.

Otherwise, you could create IF statements on your source sheet (on a new row) that will return status and % complete as long as a stage isn't at 100%, then add that info to your metrics widgets.

This is an example of a formula that would display the next stage that isn't 100% complete:

=IF([% Complete]1 < 1, [% Complete]1, IF([% Complete]4 > 1, [% complete]4))

On a new row at the top, you'll want to create an IF statement that looks at the % Complete column for stage 1 and displays it if it's less than 1 (which is less than 100%). If it's 1 (or 100%) then it moves on to the cell in stage 2 to check for the same status. You'll want to expand the IF statement to do this for all stages and make sure that the cell references match your sheet.

Here's an IF statement that would display the next status, still going off of whether the stages are 100%:

=IF([% Complete]1 < 1, [Status]1, IF([% Complete]4 > 1, [Status]4))

Similar situation, but when % complete is less than 1, you'd display the status instead.

You'll want to put these IF statements in different cells. Then you can create 2 metric widgets that display them.