Click a button and have hours spread across depts

Pamela Wagner

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!

Ryan Sides

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

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?

Pamela Wagner

Yes, this does help and I will try it. Thanks for the thought re: manually adding data but I think this may work and I don't think that is a constraint.

Ryan Sides

Great! Enjoy

Pamela Wagner

@Ryan Sides - I just got stuck. How does the above formula spread the numbers across those 5 depts? I need to wind up where the 5 hours shows up as 1 hour per each dept in their cells.

Ryan Sides

@Pamela Wagner

Place that formula in each one of your Dept cells. It will divide the number of hours in the Hours column by 5 when you click the checkbox.

Pamela Wagner

@Ryan Sides - never mind! I got it!!

Ryan Sides

Whoop Whoop!