canceling the formula aut-update feature with Arrays

G

Guest

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:J1000="M",1,0)))+SUM(IF(MANIFEST!R1:R1000="O5",IF(MANIFEST!J1:J1000="M",1,0)))+SUM(IF(MANIFEST!R1:R1000="O6",IF(MANIFEST!J1:J1000="M",1,0)))+SUM(IF(MANIFEST!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(MANIFEST!J1:J1000="M",1,0)))+SUM(IF(MANIFEST!R1:R1000="O10",IF(MANIFEST!J1:J1000="M",1,0)))+SUM(IF(ATTACHED!R1:R1000="O4",IF(ATTACHED!J1:J1000="M",1,0)))+SUM(IF(ATTACHED!R1:R1000="O5",IF(ATTACHED!J1:J1000="M",1,0)))+SUM(IF(ATTACHED!R1:R1000="O6",IF(ATTACHED!J1:J1000="M",1,0)))+SUM(IF(ATTACHED!R1:R1000="O7",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(ATTACHED!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
 
D

Dave Peterson

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)
 
Top