Predecessors with conditional calculation based on a cell value

Options
sherzog
sherzog
edited 05/16/24 in Smartsheet Basics

Dear all,
I need your help to find a way to create a conditional calculation on start days.

Fixed values for this:
- the project critical date named as "go Live" in "5SS" [Start Date] - always just a 1-day-date value
- the date calculation is based on fixed working days, such as 86d or 75d

Flexible values:
- if in [Execution] column the value is "per Hotel" the calculation needs to be f.e.
Go-Live date -86d = "5SS -86d"
- if in [Execution] column the value is "per Wave" the calculation needs to be
1. of the month of Go-Live date -86d = ????

Any idea how to manage this with an "=if…" condition or any helper column for the predecessor value to calculate the start date of a tasked based on the "execution" value?

Many thanks
Sebastian

Tags:

Best Answer

  • Jason Tarpinian
    Jason Tarpinian ✭✭✭✭✭✭
    Answer ✓
    Options

    Naming convention shouldn't need to come into play for this solution. Here's a quick example:

    1/Red = Link the task/date, whatever, to a secondary sheet

    2/Green = Calculate your variable duration/date

    3/Blue = Link the calculated duration back to the original schedule

    Jason Tarpinian - Sevan Technology

    Smartsheet Aligned Partner

Answers

  • Jason Tarpinian
    Jason Tarpinian ✭✭✭✭✭✭
    Options

    You can't build formulas in dependency enabled cells (Start Date, End Date, Duration, Predecessors).

    One trick I've used in the past is that Start Date & Duration can both be a cell link. I'm not sure if it would help in your situation, but you can link your go live date and the Execution values to another sheet, do calculations over there, then link back either a Start Date or Duration. Don't know if that could help your issue with the predecessors though?

    Jason Tarpinian - Sevan Technology

    Smartsheet Aligned Partner

  • sherzog
    Options

    Hi Jason,

    that sounds like an possible idea. How to create this reference? Just "={sheet [Column]}"?

    When I try to, I get an error message naming that due to the nature of the for example "start" column to be a date this is not possible. How to overcome this?

    Many thanks
    Sebastian

  • Jason Tarpinian
    Jason Tarpinian ✭✭✭✭✭✭
    Answer ✓
    Options

    Naming convention shouldn't need to come into play for this solution. Here's a quick example:

    1/Red = Link the task/date, whatever, to a secondary sheet

    2/Green = Calculate your variable duration/date

    3/Blue = Link the calculated duration back to the original schedule

    Jason Tarpinian - Sevan Technology

    Smartsheet Aligned Partner

  • sherzog
    Options