Using a NAME as a reference in another range
 

* The value in cell A1 varies depending on the number of rows of data brought
down in a query
* I have used the 'NAME' function to name cell A1 '_DataRows'

I now want to use '_DataRows' to identify the end of a range in a SUMPRODUCT
formula. (i.e. a formula something like this...)

=SUMPRODUCT
(--($D$1:$D$"_DataRows"=XYZ),--($D$1:$D"_DataRows"<""),--($E$1:$E"_DataRows"))


How do I do this (see examples below).

Many Thanks,

Craig

-------------------------------------------

Example 1:
---------------
If cell A1 = 25
Formula in cell B2: =SUMPRODUCT
(--($D$1:$D$25=XYZ),--($D$1:$D$25<""),--($E$1:$E$25))


Example 2:
---------------
If cell A1 = 38
Formula in cell B2: =SUMPRODUCT
(--($D$1:$D$38=XYZ),--($D$1:$D$38<""),--($E$1:$E$38))

Using a NAME as a reference in another range
 

Change each portion to something like:
=sumproduct(--(INDIRECT("$D$1:$D$" & _datarows)=XYZ), ....


If you query would return 400 lines at most, why not just use $d$1:D$1000?

(choose a number that's big enough to never be exceeded--but don't use the whole
column)






cdavidson wrote:

* The value in cell A1 varies depending on the number of rows of data brought
down in a query
* I have used the 'NAME' function to name cell A1 '_DataRows'

I now want to use '_DataRows' to identify the end of a range in a SUMPRODUCT
formula. (i.e. a formula something like this...)

=SUMPRODUCT
(--($D$1:$D$"_DataRows"=XYZ),--($D$1:$D"_DataRows"<""),--($E$1:$E"_DataRows"))

How do I do this (see examples below).

Many Thanks,

Craig

-------------------------------------------

Example 1:
---------------
If cell A1 = 25
Formula in cell B2: =SUMPRODUCT
(--($D$1:$D$25=XYZ),--($D$1:$D$25<""),--($E$1:$E$25))

Example 2:
---------------
If cell A1 = 38
Formula in cell B2: =SUMPRODUCT
(--($D$1:$D$38=XYZ),--($D$1:$D$38<""),--($E$1:$E$38))

--

Dave Peterson