stop excel automatically changing formulas

[sollidamra]

i have a summary worksheet which looks up stock from other worksheets and
totals them for me. The problem is if i insert or delete a row into my stock
excel automatically adjusts the formula range up or down.
my formula:-
{=SUM((Engineers!B2:B500="Cisco AP")*(Engineers!E2:E500))}
this looks in "Engineers" worksheet, finds all occureneces of Cisco AP's in
column B and adds up the quantities which are in column E. If I use a piece
of stock and delete the row excel changes the ranges to B2:B499 -
E2:E499, what i want is for it to stay permanently at B2:B500 - E2:E500
I was told i may need to use the INDIRECT function but it did not work
although i may have applied it incorrectly, this is what i did
{=SUM((INDIRECT(Engineers!B2:B500="Cisco AP")*(Engineers!E2:E500)))}
to be honest not sure if brackets are in right place, this returns a REF!
error.
please help

 

[JE McGimpsey]

One way (array-entered):

=SUM((INDIRECT("Engineers!B2:B500")="Cisco
AP")*(INDIRECT("Engineers!E2:E500")))


or equivalently (non-array entered):

=SUMPRODUCT(--(INDIRECT("Engineers!B2:B500")="Cisco AP"),
--(INDIRECT("Engineers!E2:E500")))

 

[JLatham]

I recommend using John McGimpsey's "non-array entered" method.

You may be getting the #REF! error if you've entered the formula in a
workbook that doesn't have a worksheet named Engineers.

As for the braces, they are put into a formula like that by Excel when you
enter the formula as an array formula. You do that by terminating the
formula with [ctrl]+[Shift]+[Enter] instead of just plain [Enter]. Also, if
you ever edit an array formula, you must again use [ctrl]+[Shift]+[Enter] to
keep it as an array formula when you finish the edit.

 

[sollidamra]

Thanks guys works a treat

I recommend using John McGimpsey's "non-array entered" method.

You may be getting the #REF! error if you've entered the formula in a
workbook that doesn't have a worksheet named Engineers.

As for the braces, they are put into a formula like that by Excel when you
enter the formula as an array formula. You do that by terminating the
formula with [ctrl]+[Shift]+[Enter] instead of just plain [Enter]. Also, if
you ever edit an array formula, you must again use [ctrl]+[Shift]+[Enter] to
keep it as an array formula when you finish the edit.

i have a summary worksheet which looks up stock from other worksheets and
totals them for me. The problem is if i insert or delete a row into my stock
excel automatically adjusts the formula range up or down.
my formula:-
{=SUM((Engineers!B2:B500="Cisco AP")*(Engineers!E2:E500))}
this looks in "Engineers" worksheet, finds all occureneces of Cisco AP's in
column B and adds up the quantities which are in column E. If I use a piece
of stock and delete the row excel changes the ranges to B2:B499 -
E2:E499, what i want is for it to stay permanently at B2:B500 - E2:E500
I was told i may need to use the INDIRECT function but it did not work
although i may have applied it incorrectly, this is what i did
{=SUM((INDIRECT(Engineers!B2:B500="Cisco AP")*(Engineers!E2:E500)))}
to be honest not sure if brackets are in right place, this returns a REF!
error.
please help