reference a range using label

[dhx10000]

Hi,

Yesterday, I learned from this board how to reference a column on a separate sheet through a label, using Insert >> Name >> Define. The newest problem is that there appear to be some formulas that require me to actually designate a more specific range rather than just designating a column. For example, I have this formula:

=SUMPRODUCT((CAK1QR0L!R5:R5003=TRUE)*(CAK1QR0L!J5:J5003= "Closed" ))

This works the way it is written but as you can see I had to go back to using absolute references to columns which i was trying to avoid. I would like to have done this, but I received an error 'The formula you typed contains an error' :

=SUMPRODUCT((BarcelonaInternalActions-logView.xls'!Current=TRUE)*(BarcelonaInternalActions-logView.xls'!Status= "Closed" ))

Is there any way to make this work?

 

[NBVC]

I don't think you can use whole columns with sumproduct (i.e. A:A or Defining the whole column)

Try re-defining names for the specific ranges R5:R5003 and J5:J5003.