Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Conversion Formulas / Cell Linking

Laura
Laura ✭✭✭✭✭✭
edited 12/09/19 in Archived 2017 Posts

I am cell-linking 2 sheets but 1 sheet records time in days and allocation:

 

Example:

2 days and 50% Allocation = total of 8 hours spent on the project over the course of 2 days.

(The duration can not be changed from days because this is our project management sheet and we use in the gantt chart to share with customers.)

 

 

The 2nd sheet is our cost sheet but we record time in hours and that can not be changed.

 

What is the simplest way I can possibly hide a column on the project management sheet that uses a formula (???) to convert the duration in days & allocation to hours, then cell link the hours result to the Cost Sheet?

 

 

Any other ideas or suggestions?

SS-Cost.png

SS-PjtMgmt.png

Comments

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Laura,

     

    Note 1. You can put your allocation in hours, but it will still calculate as days

    That is 8h = 1d for duration calculation, Gantt chart view, and the formula below.

     

    To get your hours value:

     

    a. create a column in the project sheet

    b. add the following formula:

     

    =(Duration23 * 8) * [Allocation %]23

     

    for row 23.

     

    Note 2: This will return 0 if either Duration or Allocation % is blank.

     

    3. Copy that formula to the rest of the column.

     

    4. Link that to your other sheet and then hide away.

     

    Note 3: The eight (8) above assumes your work-day is set to 8 hours - that is configurable in the Project Settings. If you work-day is 10 hours, update as appropriate.

     

    Hope this helps.


    Craig

     

     

  • Laura
    Laura ✭✭✭✭✭✭

    Well that was easy enough! 

    Thank you!

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    I just make it look easy. Cool

     

    (That's not true - it is)


    Craig

This discussion has been closed.