Click a button and have hours spread across depts

I am trying to figure out how to automate taking one number and spreading it across a number of different cells. I have created a sheet where I track my time and allocate it out across the depts I support. There are over 15 depts and 8 of those are very common to spread time out to as I run projects for them specifically. My vision is that in the Hours column, I would put in say, 5 hours, then click a checkbox in the call center column and then it will calculate the number of hours divided by the number of call centers (in this case 8 call centers divided by 5 hours) and put that answer in the cells under their depts. Here is a simple screenshot:

I made this example even more simple where I need to spread out my 5 hours over 5 depts so after clicking the box under CCs, 1h will appear in each of those depts.

I feel that there is some way to do this but not sure how!

Best Answer

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Answer ✓

    Hi @Pamela Wagner

    Are you looking to also manually update those cells in Dept1, etc.? If you don't need to do that, you can write a formula that goes in each Dept's cell. Something like...

    =if( CCs@row = 1, Hours@row/5, "")

    Where 5 is your number of Depts.

    Keep in mind that if you make this a Column formula (where is fills in for you automatically), you will not be able to manually enter any values under the Dept columns.

    But if you just copy/paste the formula down your sheet, you can manually update the cells by deleting the formula in that cell.

    Does that help?

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!