Keeping formuls when rows are added or delete

Microsoft Excel 2003 (full)
March 8, 2010 at 14:31:06
Specs: Windows XP
I have the formula on a chart on a sheet referencing data on another.:
=SUMPRODUCT(('Open Issues'!$C$2:$C$50=5)*('Open Issues'!$F$2:$F$50=$B$39)+('Closed Issues'!$C$2:$C$50=5)*('Closed Issues'!$F$2:$F$50=$B$39))

Every time I delete or add a row on sheet 'Open or Closed Issues' I get the following error.

Any Ideas on how to prevent this?

See More: Keeping formuls when rows are added or delete

March 8, 2010 at 18:55:19
You have to change
=SUMPRODUCT(('Open Issues'!$C$2:$C$50=5)*('Open Issues'!$F$2:$F$50=$B$39)+('Closed Issues'!$C$2:$C$50=5)*('Closed Issues'!$F$2:$F$50=$B$39))


=SUMPRODUCT(('Open Issues.xls'!$C$2:$C$50=5)*('Open Issues.xls'!$F$2:$F$50=$B$39)+('Closed Issues.xls'!$C$2:$C$50=5)*('Closed Issues.xls'!$F$2:$F$50=$B$39))

This should work.

Report •

March 9, 2010 at 11:40:25
Thank you but, these are seperate sheets/tabs on the same Excel spreadsheet file.

Report •

March 9, 2010 at 14:12:02

As the ranges are on different sheets, when you add or delete a row on one sheet, the number of cells referred to in each part of the formula is no longer equal.

In SUMPRODUCT() each part must reference the same number of cells.

When I tried this, I started with ranges of C2 to C50 and F2 to F50 on both worksheets, but deleting a row on the Closed Issues worksheet changed the reference to C2 to C49 and F2 to F49, but the ranges on the Open Issues worksheet remained 2 to 50.

Hence the NA error.

I was able to handle this by using change events on the two worksheets Open / Closed Issues and using named ranges.

First I defined four named ranges, rng1, rng2, rng3 and rng4
These were rng1 and rng2 for Open Issues C2 to C50 and F2 to F50
and rng3 and rng4 for Closed Issues C2 to C50 and F2 to F50

The formula is now rewritten as:


Goto the Closed Issues worksheet and right-click on the Tab name and select view code.

Add this code to the VB window that opens:

Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveWorkbook.Names("rng3").RefersTo = ActiveSheet.Range("C2:C50")
    ActiveWorkbook.Names("rng4").RefersTo = ActiveSheet.Range("F2:F50")
End Sub

Do the same on the Open Issues worksheet using rng1 and rng2 in place of rng3 and rng4

Now, whenever a row is added or deleted on either sheet, the ranges remain set to rows 2 to 50.

You will have to determine if this still returns the required results from your formula, but you don't get NA errors.

I didn't do any more than basic code here - it would probably be appropriate to ensure that this code only runs when rows are added or deleted and probably you should disable change events while the code is running.

If this works for you, these extras could be added to make the code more robust.


Report •
Related Solutions

Ask Question