# Use case for timesheet in Smartsheet

✭✭✭✭✭✭

Hello everyone,

I have the following use case that I would like to implement in Smartsheet but I am not sure what is the best option :

• I have a sheet for each employee in my organization, where they are filling incurred time (in days) for each month for each project they are working on.

• I have a master sheet with all the projects and ressources where I would like to compare incurred and planified time at year level for each resource. For planified time I just take data from the projects sheets so it is quite easy, but for incurred time I need to take the sum of incurred time from each resource (that means from each sheet).

The only option I found is to put a formula that verifies who is the resource and then take the incurred time from the specific sheet of the resource. But that means I will need to do that for each resource of my organization (20+) so the formula will be very long and complex.

Another option would have been to put all the incurred time for all the resource in one place (a report for example), but I after that I cannot do formulas that take data from a report..

Thank you very much for your help !

Corentin

• ✭✭✭✭✭✭

PMP Certified

[email protected]

www.mobilproject.it

• ✭✭✭✭✭✭

Hello @Bassam.M Khalil

Yes absolutely 😉 , for the first sheet I have the following columns :

• Assigné à : assigned to
• Nom du projet/Chantier transverse : project name
• The months
• The sum at the very end

The idea is to have the number of days incurred on one year (here 2021) categorized by project name

For the second sheet :

• Assigné à : assigned to
• Nom du projet/Chantier transverse : project name
• 2021 Planifié : number of days planned (calculated automatically)
• 2021 Effectif : number of days incurred (that I want to get from the first sheet)

For the forumla here are the arguments :

• =IF([Assigned to]@row = "ressource name"; SUMIF({project name column range}; [project name]@row;{sum of days incurred from first sheet})

Thanks again, have a great day,

Corentin

• ✭✭✭✭✭✭

I'd recommend summing the information somewhere in each sheet and then use cell-linking to collect everything either to the Master Sheet or, in a so-called Master Metrics Sheet, and then use a cross-sheet formula to collect it to your Master Sheet.

Make sense?

Would that work/help?

I hope that helps!

Have a fantastic week & Happy Holidays!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭

Great IDEA @Andrée Starå

PMP Certified

[email protected]

www.mobilproject.it

• ✭✭✭✭✭✭
edited 12/22/20

@Andrée Starå and @Bassam.M Khalil thank you for your help

I am not sure to understand how to do this :

I cannot sum the information because I need to have the sum of days incurred by project (by row)

If I understand well, regardless of the option I choose, I will need to take some time to link each and every resource timesheet (20+) to a central mastersheet with cell link or formulas.

I was wondering if we were not able to transform a report into a sheet to use formulas from this data, because a report for all the resources is easier to implement than a mastersheet / metric sheet.

Thank you for your help anyway,

Corentin