I have a vacation/training tracking spreadsheet where there are 5 rows for
each associate. The body of the section has month titles (jan - dec) and
below each is a cell for training, holiday, vacation. In the 5th row, there
is a utilization percent calculated. All of this is summarized in a section
below the associates which calculates overall team utilization.

The problem I have is that when I copy/insert a new associate section, the
formulas below break and I have to manual enter the cell locations for each
month's training/holiday/vacation.

Can this be automated?

Sample of associate section:

January February
Training
Holiday 1
Vac/Prsn
Util % 95.45% 100.00%

Sample of totals section:
January February
Days OOTO 9 0
HPM 176 160
HPM*Emp 1584 1440
Hrs OOTO 72 0
TeamUtil % 95.45% 100.00%

Formula that breaks when I insert new associate:

Days OOTO for January
=SUM(E6,E7,E8,E12,E13,E14,E18,E19,E20,E24,E25,E26, E30,E31,E32,E36,E37,E38,E42,E43,E44,E48,E49,E50,E5 4,E55,E56)

Hi,

When you say "breaks" you mean it does not include the new entries? If that
is the case, that is what would happen with the formula you are using.
Instead you might try a formula like this:

=SUMPRODUCT(--(MOD(ROW(6:56),6)<3),E6:E56)

Now if you insert the new 6 rows between row 6 and 56 the formula will
capture them.

--
Thanks,
Shane Devenshire


"David" wrote:

I have a vacation/training tracking spreadsheet where there are 5 rows for
each associate. The body of the section has month titles (jan - dec) and
below each is a cell for training, holiday, vacation. In the 5th row, there
is a utilization percent calculated. All of this is summarized in a section
below the associates which calculates overall team utilization.

The problem I have is that when I copy/insert a new associate section, the
formulas below break and I have to manual enter the cell locations for each
month's training/holiday/vacation.

Can this be automated?

Sample of associate section:

January February
Training
Holiday 1
Vac/Prsn
Util % 95.45% 100.00%

Sample of totals section:
January February
Days OOTO 9 0
HPM 176 160
HPM*Emp 1584 1440
Hrs OOTO 72 0
TeamUtil % 95.45% 100.00%

Formula that breaks when I insert new associate:

Days OOTO for January
=SUM(E6,E7,E8,E12,E13,E14,E18,E19,E20,E24,E25,E26, E30,E31,E32,E36,E37,E38,E42,E43,E44,E48,E49,E50,E5 4,E55,E56)

Okay, I like where you are going with this...issue is that the "totals"
section is directly below the associates section, so if I insert the 5 rows
for the new associate, the totals section shifts down and the new associate
rows are below row 56.

"ShaneDevenshire" wrote:

Hi,

When you say "breaks" you mean it does not include the new entries? If that
is the case, that is what would happen with the formula you are using.
Instead you might try a formula like this:

=SUMPRODUCT(--(MOD(ROW(6:56),6)<3),E6:E56)

Now if you insert the new 6 rows between row 6 and 56 the formula will
capture them.

--
Thanks,
Shane Devenshire


"David" wrote:

I have a vacation/training tracking spreadsheet where there are 5 rows for
each associate. The body of the section has month titles (jan - dec) and
below each is a cell for training, holiday, vacation. In the 5th row, there
is a utilization percent calculated. All of this is summarized in a section
below the associates which calculates overall team utilization.

The problem I have is that when I copy/insert a new associate section, the
formulas below break and I have to manual enter the cell locations for each
month's training/holiday/vacation.

Can this be automated?

Sample of associate section:

January February
Training
Holiday 1
Vac/Prsn
Util % 95.45% 100.00%

Sample of totals section:
January February
Days OOTO 9 0
HPM 176 160
HPM*Emp 1584 1440
Hrs OOTO 72 0
TeamUtil % 95.45% 100.00%

Formula that breaks when I insert new associate:

Days OOTO for January
=SUM(E6,E7,E8,E12,E13,E14,E18,E19,E20,E24,E25,E26, E30,E31,E32,E36,E37,E38,E42,E43,E44,E48,E49,E50,E5 4,E55,E56)