G
Glen Mettler
I have the following formula to calculate some numeric data:
=SUMIF(F6:F27,F5,S6:S27) (this is in cell S5)
the qualifying code is in column F and the numeric data to sum is in column
S
Problem. When I insert a row just below row 5, the formula in S5 changes
to:
=SUMIF(F7:F27,F5,S7:S27)
I will be adding data to the new row6 and I need it to become a part of the
calculation.
I need the formula to remain F6: and S6:
Peo Sjoblom gave me this fix:
=SUMIF(INDIRECT("F6:F27"),F5,INDIRECT("S6:S27"))
I can't use it. Here is a further explanation:
I have criteria that engineers enter tasks for. They enter a task via a
menu option I have created. A macro enters a row and populates various
cells with formula and finally places the cursor in the Description column
for editing/creating by the engineer.
After creating the description of the task, the engineer enters hours for
the task (column S). The engineer can enter several tasks to support a
selected criteria.
Therefore, I need the formula to be dynamic downword but the original
formula in the criteria to remain the same.
So if Criteria 1 has formula: =SUMIF(F6:F27,F5,S6:S27) (cell S5), it needs
to remain so after any number of task entries below it (say 3)
Criteria 2 would have an intial formula of =SUMIF(F7:F27,F6,S7:S27) (in
cell S6) prior to adding rows. After rows are added for Criteria 1,
Criteria 2 (now row 9) should have an adjusted formula of
=SUMIF(F10:F27,F9,S10:S27)
Is this possible?
(Hard to explain and visualize w/o a working workbook)
Glen
=SUMIF(F6:F27,F5,S6:S27) (this is in cell S5)
the qualifying code is in column F and the numeric data to sum is in column
S
Problem. When I insert a row just below row 5, the formula in S5 changes
to:
=SUMIF(F7:F27,F5,S7:S27)
I will be adding data to the new row6 and I need it to become a part of the
calculation.
I need the formula to remain F6: and S6:
Peo Sjoblom gave me this fix:
=SUMIF(INDIRECT("F6:F27"),F5,INDIRECT("S6:S27"))
I can't use it. Here is a further explanation:
I have criteria that engineers enter tasks for. They enter a task via a
menu option I have created. A macro enters a row and populates various
cells with formula and finally places the cursor in the Description column
for editing/creating by the engineer.
After creating the description of the task, the engineer enters hours for
the task (column S). The engineer can enter several tasks to support a
selected criteria.
Therefore, I need the formula to be dynamic downword but the original
formula in the criteria to remain the same.
So if Criteria 1 has formula: =SUMIF(F6:F27,F5,S6:S27) (cell S5), it needs
to remain so after any number of task entries below it (say 3)
Criteria 2 would have an intial formula of =SUMIF(F7:F27,F6,S7:S27) (in
cell S6) prior to adding rows. After rows are added for Criteria 1,
Criteria 2 (now row 9) should have an adjusted formula of
=SUMIF(F10:F27,F9,S10:S27)
Is this possible?
(Hard to explain and visualize w/o a working workbook)
Glen