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

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?
Answers

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?

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  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  never mind! I got it!!
Help Article Resources
Categories
Check out the Formula Handbook template!