canceling the formula aut-update feature with Arrays
 

I am using a spreadsheet to track a lot of Personnel across a lot of
categories.
The problem is when I move a person (row) to another sheet, Excel updates
rages. This has caused problems with the summary page.
This one of the formulas:
{=SUM(IF(MANIFEST!R1:R1000="O4",IF(MANIFEST!J1:J10 00="M",1,0)))+SUM(IF(MANIFEST!R1:R1000="O5",IF(MAN IFEST!J1:J1000="M",1,0)))+SUM(IF(MANIFEST!R1:R1000 ="O6",IF(MANIFEST!J1:J1000="M",1,0)))+SUM(IF(MANIF EST!R1:R1000="O7",IF(MANIFEST!J1:J1000="M",1,0)))+ SUM(IF(MANIFEST!R1:R1000="O8",IF(MANIFEST!J1:J1000 ="M",1,0)))+SUM(IF(MANIFEST!R1:R1000="09",IF(MANIF EST!J1:J1000="M",1,0)))+SUM(IF(MANIFEST!R1:R1000=" O10",IF(MANIFEST!J1:J1000="M",1,0)))+SUM(IF(ATTACH ED!R1:R1000="O4",IF(ATTACHED!J1:J1000="M",1,0)))+S UM(IF(ATTACHED!R1:R1000="O5",IF(ATTACHED!J1:J1000= "M",1,0)))+SUM(IF(ATTACHED!R1:R1000="O6",IF(ATTACH ED!J1:J1000="M",1,0)))+SUM(IF(ATTACHED!R1:R1000="O 7",IF(ATTACHED!J1:J1000="M",1,0)))+SUM(IF(ATTACHED !R1:R1000="O8",IF(ATTACHED!J1:J1000="M",1,0)))+SUM (IF(ATTACHED!R1:R1000="09",IF(ATTACHED!J1:J1000="M ",1,0)))+SUM(IF(ATTACHED!R1:R1000="O10",IF(ATTACHE D!J1:J1000="M",1,0)))}

Yeah...I know....crazy. I didn't write it, I inherited it.
But I need it to stop updating the formulas when I delete/add rows to the
sheets.

Thanks
CPT Marshall Tway
US ARMY
Deployed somewhere

canceling the formula aut-update feature with Arrays
 

That long formula could be replaced by:

=SUMPRODUCT((manifest!R1:R1000={"o4","o5","o6","o7 ","o8","o9","o10"})
*(manifest!J1:J1000="m"))
+
SUMPRODUCT((attached!R1:R1000={"o4","o5","o6","o7" ,"o8","o9","o10"})
*(attached!J1:J1000="m"))

(no need to array enter it)

I'm not quite sure how changing the range screws up the formula, but you could
use =indirect("manifest!r1:r1000") to always point to that range:

=SUMPRODUCT((INDIRECT("manifest!R1:R1000")
={"o4","o5","o6","o7","o8","o9","o10"})*(INDIRECT( "manifest!J1:J1000")="m"))
+
SUMPRODUCT((INDIRECT("attached!R1:R1000")
={"o4","o5","o6","o7","o8","o9","o10"})*(INDIRECT( "attached!J1:J1000")="m"))

(all one cell)

Marshall.tway wrote:

I am using a spreadsheet to track a lot of Personnel across a lot of
categories.
The problem is when I move a person (row) to another sheet, Excel updates
rages. This has caused problems with the summary page.
This one of the formulas:
{=SUM(IF(MANIFEST!R1:R1000="O4",IF(MANIFEST!J1:J10 00="M",1,0)))+SUM(IF(MANIFEST!R1:R1000="O5",IF(MAN IFEST!J1:J1000="M",1,0)))+SUM(IF(MANIFEST!R1:R1000 ="O6",IF(MANIFEST!J1:J1000="M",1,0)))+SUM(IF(MANIF EST!R1:R1000="O7",IF(MANIFEST!J1:J1000="M",1,0)))+ SUM(IF(MANIFEST!R1:R1000="O8",IF(MANIFEST!J1:J1000 ="M",1,0)))+SUM(IF(MANIFEST!R1:R1000="09",IF(MANIF EST!J1:J1000="M",1,0)))+SUM(IF(MANIFEST!R1:R1000=" O10",IF(MANIFEST!J1:J1000="M",1,0)))+SUM(IF(ATTACH ED!R1:R1000="O4",IF(ATTACHED!J1:J1000="M",1,0)))+S UM(IF(ATTACHED!R1:R1000="O5",IF(ATTACHED!J1:J1000= "M",1,0)))+SUM(IF(ATTACHED!R1:R1000="O6",IF(ATTACH ED!J1:J1000="M",1,0)))+SUM(IF(ATTACHED!R1:R1000="O 7",IF(ATTACHED!J1:J1000="M",1,0)))+SUM(IF(ATTACHED !R1:R1000="O8",IF(ATTACHED!J1:J1000="M",1,0)))+SUM (IF(ATTACHED!R1:R1000="09",IF(ATTACHED!J1:J1000="M ",1,0)))+SUM(IF(ATTACHED!R1:R1000="O10",IF(ATTACHE D!J1:J1000="M",1,0)))}

Yeah...I know....crazy. I didn't write it, I inherited it.
But I need it to stop updating the formulas when I delete/add rows to the
sheets.

Thanks
CPT Marshall Tway
US ARMY
Deployed somewhere

--

Dave Peterson