Use case for timesheet in Smartsheet

Corentin Dal Farra

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..

Do you have any ideas or suggestions about this ?

Thank you very much for your help !

Corentin

Bassam Khalil

Hi @Corentin Dal Farra 

Hope you are fine, i can help you if you translate your sheet to English😂

Corentin Dal Farra

Hello @Bassam.M Khalil

Thank you for your answer !

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})

Let me know if I can help you with more elements,

Thanks again, have a great day,

Corentin

Andrée Starå

Hi @Corentin Dal Farra

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

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Bassam Khalil

Great IDEA @Andrée Starå

Corentin Dal Farra

@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