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