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..
Do you have any ideas or suggestions about this ?
Thank you very much for your help !
Corentin
Answers
-
Hope you are fine, i can help you if you translate your sheet to English😂
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
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
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | 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å
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
@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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives