software

Good Afternoon:

I have input the ranges as quot;M2:M102quot;; this seems to work. For whatever
reason the sumproduct formulae don't like the named ranges, which are defined
as quot;M2:M965quot;. Large ranges to allow for growth.

I will need to edit the ranges every month.

Thanks for input.

quot;dave rothquot; 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.