Timeline Variance indicators

Zain

Hi,

I am new to using Smartsheet and was wanted to confirm a few things timelines related:

1. Can Smartsheet create predecessor and successor dependencies (FF, FS, SS, SF) similar to MS Project?
2. Does Smartsheet allow for WBS elements to be created through indent / outdent similar to MS Project?
3. Can Smartsheet allow linking to other timeline sheets that have the same titled milestones/activity types and establish automatic variance indicators (red= dates off by 30 days between two sheets/milestones, yellow= dates off by 15 days between two sheets/milestones, green = in sync or dates off by 7 days between two sheets/ same milestones)? So in one main sheet if I have a row called Manufacturing 123 and in another functional sheet I have a row called Manufacturing 123, I can link the two milestones together based on the name/activity type and if the functional sheet date changes then I get the variance indicator(s) on the main sheet displayed in a new column titled Variance? Is this possible to do in Smartsheet? Do I need a specific plug in for this? Any example would be helpful.
4. Can timeline sheets feed into custom reports/dashboards? Does Smartsheet link to Power BI? What would you recommend as a good dashboard plugin?

Any help would be appreciated on how to get started. Thanks!

Genevieve P.

Hi @Zain

1) Yes, you can identify predecessor rows and relationships in a Project in Smartsheet. See: Enable Dependencies and Use Predecessors

I would suggest reviewing this free Webinar that goes through Smartsheet's Project settings: SmartStart: Project Management

2) I don't know MS Project well so I can't speak to their functionality, but you can create what's called a Hierarchy in Smartsheet with Parent and Child rows (indented rows). See: Hierarchy: Indent or Outdent Rows. In a Project Sheet, the Parent row has a Parent Rollup Functionality.

3) This one is a little trickier without seeing your specific sheet/project set up. You can link cells together across sheets either with cross-sheet formulas or through Cell Linking, but it actually sounds like a Report would be better suited to your needs.

You can create a Report that brings in rows from multiple sheets (depending on your Plan Type), based on a filter (ex. is a Milestone). This would then show you all milestone rows from all of your sheets. Then you could Group it by the Task Name so you can see the duplicate Milestone names together.

See: SmartStart: Reporting and Redesigned Reports with Grouping and Summary Functions

4) Yes, you can create Reports and Dashboards out of Smartsheet sheets, including Project sheets. There are a number of Template Sets that show how you could configure this type of custom Dashboard... for example, see: Project Tracking and Rollup Template Set

Here's the Dashboard Webinar: SmartStart: Dashboards

I would suggest that the different SmartStart Webinars are a great way to get started, along with the different Learning Tracks in the Help Center (https://help.smartsheet.com/). If your plan has access to it, the Smartsheet University contains a number of eLearning courses that would be really helpful as well.

Cheers,

Genevieve

Genevieve P.

Hi @Zain

There are two things to clarify with your formula:

1) What do you want it to do between the numbers of 7 and 15? Currently there is no statement for any colour between 7 and 15 so the cell will be blank, is that the correct response?

2) The reason you're getting an error is because the formula doesn't know the relationship between Helper 1 and Helper 2. Is this an AND statement (so both cells have to be in that range) or is it an OR statement (so if ONE of those cells is in that range it auto-does the first statement that matches).

I presume you want each of these to be an OR statement, so try this:

=IF(OR(ISBLANK([Variance helper 1]@row), ISBLANK([Variance helper 2]@row)), "", IF(OR([Variance helper 1]@row > 30, [Variance helper 2]@row > 30), "Red", IF(OR([Variance helper 1]@row > 15, [Variance helper 2]@row > 15), "Yellow", IF(OR([Variance helper 1]@row <= 7, [Variance helper 2]@row <= 7), "Green"))))

Keep in mind that if Helper 1 is 30 but Helper 2 is blank it will return blank, since that's the first statement.

Let me know if this gives you the desired output! If not, it would be helpful to know when you want each colour to appear.

Cheers,

Genevieve

Genevieve P.

Hi @Zain

1) Yes, you can identify predecessor rows and relationships in a Project in Smartsheet. See: Enable Dependencies and Use Predecessors

I would suggest reviewing this free Webinar that goes through Smartsheet's Project settings: SmartStart: Project Management

2) I don't know MS Project well so I can't speak to their functionality, but you can create what's called a Hierarchy in Smartsheet with Parent and Child rows (indented rows). See: Hierarchy: Indent or Outdent Rows. In a Project Sheet, the Parent row has a Parent Rollup Functionality.

3) This one is a little trickier without seeing your specific sheet/project set up. You can link cells together across sheets either with cross-sheet formulas or through Cell Linking, but it actually sounds like a Report would be better suited to your needs.

You can create a Report that brings in rows from multiple sheets (depending on your Plan Type), based on a filter (ex. is a Milestone). This would then show you all milestone rows from all of your sheets. Then you could Group it by the Task Name so you can see the duplicate Milestone names together.

See: SmartStart: Reporting and Redesigned Reports with Grouping and Summary Functions

4) Yes, you can create Reports and Dashboards out of Smartsheet sheets, including Project sheets. There are a number of Template Sets that show how you could configure this type of custom Dashboard... for example, see: Project Tracking and Rollup Template Set

Here's the Dashboard Webinar: SmartStart: Dashboards

I would suggest that the different SmartStart Webinars are a great way to get started, along with the different Learning Tracks in the Help Center (https://help.smartsheet.com/). If your plan has access to it, the Smartsheet University contains a number of eLearning courses that would be really helpful as well.

Cheers,

Genevieve

Zain

Super helpful @Genevieve P. ! Thank you so much!!

Can you please help me with this formula below.. I am currently using a "Variance indicator" column that has (red, yellow and green bubbles) and also a "Variance helper" column that based on a difference of 30 days, 15 days, or 7 days, the bubbles would appear accordingly.

=IF(ISBLANK([Variance helper 1]@row), "", IF([Variance helper 1]@row > 30, "Red", IF([Variance helper 1]@row > 15, "Yellow", IF([Variance helper 1]@row <= 7, "Green"))))

The above formula works but now I created another column called "Variance helper 2" that I would like to add the formula.

Can you please help fix the formula below to incorporate "Variance helper 2". I attempted below and it is giving me an error:

=IF(ISBLANK([Variance helper 1]@row), [Variance helper 2]@row) "", IF([Variance helper 1]@row, [Variance helper 2]@row) > 30, "Red", IF([Variance helper 1]@row, [Variance helper 2]@row) > 15, "Yellow", IF([Variance helper 1]@row, [Variance helper 2]@row) <= 7, "Green"))))

Genevieve P.

Hi @Zain

There are two things to clarify with your formula:

1) What do you want it to do between the numbers of 7 and 15? Currently there is no statement for any colour between 7 and 15 so the cell will be blank, is that the correct response?

2) The reason you're getting an error is because the formula doesn't know the relationship between Helper 1 and Helper 2. Is this an AND statement (so both cells have to be in that range) or is it an OR statement (so if ONE of those cells is in that range it auto-does the first statement that matches).

I presume you want each of these to be an OR statement, so try this:

=IF(OR(ISBLANK([Variance helper 1]@row), ISBLANK([Variance helper 2]@row)), "", IF(OR([Variance helper 1]@row > 30, [Variance helper 2]@row > 30), "Red", IF(OR([Variance helper 1]@row > 15, [Variance helper 2]@row > 15), "Yellow", IF(OR([Variance helper 1]@row <= 7, [Variance helper 2]@row <= 7), "Green"))))

Keep in mind that if Helper 1 is 30 but Helper 2 is blank it will return blank, since that's the first statement.

Let me know if this gives you the desired output! If not, it would be helpful to know when you want each colour to appear.

Cheers,

Genevieve

Zain

Hi @Genevieve P.

This was incredibly helpful, the OR formula worked and it is what I was looking for!

Zain

Hi @Genevieve P.

Can you please help me edit the formula below to replace the 15 days for variance helper 1 and variance helper 2 to write 30-90 days? I am not sure how to write "in between" for the formula below.

=IF(OR(ISBLANK([Variance helper 1]@row), ISBLANK([Variance helper 2]@row)), "", IF(OR([Variance helper 1]@row > 30, [Variance helper 2]@row > 30), "Red", IF(OR([Variance helper 1]@row > 15, [Variance helper 2]@row > 15), "Yellow", IF(OR([Variance helper 1]@row <= 7, [Variance helper 2]@row <= 7), "Green"))))

Zain

Hello @Genevieve P.

Just following up from my above post. Please let me know if you can help!

Thanks!

Genevieve P.

Hi @Zain

My apologies for missing your comment above!

Would you be able to detail the exact results you're looking to do?

It sounds like you want to change this:

IF(OR([Variance helper 1]@row > 30, [Variance helper 2]@row > 30), "Red",

to this:

IF(OR(AND([Variance helper 1]@row > 30, [Variance helper 1]@row < 90), AND([Variance helper 2]@row > 30, [Variance helper 2]@row < 90)), "Red",

But then what would you like the status to show if it's over 90 days?

Zain

Hi @Genevieve P.

I was able to use the formula you adjusted to give me an output of Yellow for 30-90 days. Greater than 90 days will show as Red. Thank you for your help!!

Genevieve P.

I'm glad to hear this! Thanks for letting me know it works. 🙂