software

Good Afternoon:

I have several Sumproduct formulae (from this board) that worked on my
calendar 2005 worksheet with named ranges which no longer work on the 2006
worksheet with named ranges. See below for examples:

=SUMPRODUCT(--(Sex_2005=quot;Malequot;),--(InjuryType1_2005=quot;Gunshotquot;)); this works
fine on the worksheet named quot;2005_Dataquot;. I have new data on a new worksheet
named quot;2006_Dataquot; in the same workbook; the formulae have been edited to
reflect the new named ranges, and return the #VALUE! See an example below:

=SUMPRODUCT(--(Ethnic_2006=quot;Hispanicquot;),--(InjuryType1_2006=quot;Gunshotquot;)).

Still running MSExcel 2003, the ranges are, for example,
='2006_Data'!$O$2:$O$965 is defined as quot;Ethnic_2006quot;, quot;InjuryType1_2006quot;,
etc. Simple countif and Average formulae continue to calculate on the new
worksheet.

The sheet was added to the workbook as Insert--gt;Worksheet, then the data was
pasted from another workbook (.txt).

All assistance is greatly appreciated. Many Thanks in advance.

Since you are on Excel 2003, convert the relevant data area into a list
by means of Data|List|Create List then adjust the formulas to refer to
the ranges in the relevant lists.

dave roth wrote:
gt; Good Afternoon:
gt;
gt; I have several Sumproduct formulae (from this board) that worked on my
gt; calendar 2005 worksheet with named ranges which no longer work on the 2006
gt; worksheet with named ranges. See below for examples:
gt;
gt; =SUMPRODUCT(--(Sex_2005=quot;Malequot;),--(InjuryType1_2005=quot;Gunshotquot;)); this works
gt; fine on the worksheet named quot;2005_Dataquot;. I have new data on a new worksheet
gt; named quot;2006_Dataquot; in the same workbook; the formulae have been edited to
gt; reflect the new named ranges, and return the #VALUE! See an example below:
gt;
gt; =SUMPRODUCT(--(Ethnic_2006=quot;Hispanicquot;),--(InjuryType1_2006=quot;Gunshotquot;)).
gt;
gt; Still running MSExcel 2003, the ranges are, for example,
gt; ='2006_Data'!$O$2:$O$965 is defined as quot;Ethnic_2006quot;, quot;InjuryType1_2006quot;,
gt; etc. Simple countif and Average formulae continue to calculate on the new
gt; worksheet.
gt;
gt; The sheet was added to the workbook as Insert--gt;Worksheet, then the data was
gt; pasted from another workbook (.txt).
gt;
gt; All assistance is greatly appreciated. Many Thanks in advance.