Home

Time Management Tool for resource planning

%3CLINGO-SUB%20id%3D%22lingo-sub-259978%22%20slang%3D%22en-US%22%3ETime%20Management%20Tool%20for%20resource%20planning%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-259978%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20a%20formula%20to%20equally%20divide%20a%20number%20from%20column%20A%20across%20a%20variable%20number%20of%20cells%20(%20could%20be%20any%20number%20of%20cells%20from%20from%201%20to%2052).%20The%20starting%20column%20could%20also%20be%20variable%20from%20Column%20B%20to%20Column%20ZZ%2C%20and%20the%20ending%20column%20could%20be%20the%20same%20as%20the%20starting%20column%20and%20go%20as%20far%20as%20column%20ZZ.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20tricky%20part%20is%20I'd%20like%20to%20b3%20able%20to%20manually%20override%20any%20specific%20cell(s)%20by%20entering%20a%20number%2C%20and%20have%20all%20other%20cells%20in%20the%20row%20adjust%20to%20any%20manual%20entry(ies)%20so%20the%20sum%20total%20always%20is%20equal%20to%20Column%20A.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20spreadsheet%20is%20being%20used%20for%20time%20management%20on%20a%20weekly%20basis%20for%20up%20to%20a%20year.%20Individual%20employees%20might%20only%20have%205%20or%20so%20rows%20in%20the%20Time%20Management%20Plan%20or%2020%20or%20more%20rows%20depending%20on%20how%20many%20Project%20Items%20they%20are%20working%20on.%20The%20formulas%20will%20be%20used%20as%20part%20of%20a%20larger%20spreadsheet%20including%20recurring%20meetings%20and%20logistical%2Fmanagement%20time%20in%20order%20to%20address%20employee%20capacity%20for%20resourcing%20new%20work%20items%20projected%20over%20the%20upcoming%20year.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-259978%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-276153%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20Management%20Tool%20for%20resource%20planning%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-276153%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Philip%2C%20thanks%20for%20taking%20a%20swing%20at%20it.%20It%20does%20help%20some%20with%20a%20nudge%20in%20the%20direction%20I%20want%20to%20go.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-260211%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20Management%20Tool%20for%20resource%20planning%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-260211%22%20slang%3D%22en-US%22%3E%3CP%3EHia%2C%3C%2FP%3E%3CP%3EI%20think%20I%20know%20what%20you%20are%20trying%20to%20do%2C%20but%20I%20cant%20think%20of%20a%20really%20clean%20way%20to%20do%20it.%20I%20think%20most%20of%20the%20time%20you%20are%20going%20to%20run%20into%20problems%20of%20circular%20references..%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EI%20have%20attached%20something%20close%20(I%20think).%20It's%20obviously%20not%20the%20complete%20thing%2C%20but%20it's%20maybe%20a%20nudge%20in%20a%20direction%20that%20gets%20you%20what%20you%20need.%20It%20does%20rely%20on%20a%20second%20set%20'override'%20cells%20where%20you%20can%20put%20your%20manual%20adjustments%20in.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Allain McCallum
Occasional Contributor

I need a formula to equally divide a number from column A across a variable number of cells ( could be any number of cells from from 1 to 52). The starting column could also be variable from Column B to Column ZZ, and the ending column could be the same as the starting column and go as far as column ZZ.

 

The tricky part is I'd like to b3 able to manually override any specific cell(s) by entering a number, and have all other cells in the row adjust to any manual entry(ies) so the sum total always is equal to Column A.

 

The spreadsheet is being used for time management on a weekly basis for up to a year. Individual employees might only have 5 or so rows in the Time Management Plan or 20 or more rows depending on how many Project Items they are working on. The formulas will be used as part of a larger spreadsheet including recurring meetings and logistical/management time in order to address employee capacity for resourcing new work items projected over the upcoming year. 

2 Replies

Hia,

I think I know what you are trying to do, but I cant think of a really clean way to do it. I think most of the time you are going to run into problems of circular references..


I have attached something close (I think). It's obviously not the complete thing, but it's maybe a nudge in a direction that gets you what you need. It does rely on a second set 'override' cells where you can put your manual adjustments in.

Hi Philip, thanks for taking a swing at it. It does help some with a nudge in the direction I want to go.