Help With Formula (Using A Reference In A Range)

[CaliKidd]

I have a simple Excel 2003 workbook, consisting of 4 sheets named REF, CAT 1, CAT 2, and Summary.

On the Summary Sheet, I have the following formula:

=COUNTIF('CAT 2'!A5:A65536,'CAT 1'!A1)

Basically, it just tallies the number of matches it finds in the range A5:A65536 of the CAT 2 sheet using a criteria I've placed in cell A1 of the CAT 1 sheet.

Here's the problem:
In the formula, instead of referring to the last row explicitly (65536), I would like to use a reference to cell A1 of the REF sheet. I would use Application.Rows.count to place the last row value in cell A1 of the REF sheet so that if someone was using Excel 2007 or higher, the value of that cell would be 1048576, not 65536.

I tried the following combinations using a concatenation approach (my modifications in red), but they didn't work:
=COUNTIF('CAT 2'!A5:A & REF!A1,'CAT 1'!A1)

=COUNTIF("'CAT 2'!A5:A" & REF!A1,'CAT 1'!A1)

=COUNTIF('CAT 2'!"A5:A" & REF!A1,'CAT 1'!A1)

=COUNTIF('CAT 2'!"A5:A & REF!A1",'CAT 1'!A1)

Can someone please tell me what the formula (in A1 format, not R1C1 format) should be?

 

[Jonmo1]

I think I read somewhare that Index becomes volatile when you do
A1:INDEX(...)

 

[T. Valko]

I think I read somewhare that Index becomes volatile when you do
A1:INDEX(...)

Not really volatile in the sense that it recalculates at EVERY triggered calculation.

Charles Williams did an evaluation of this a while back. I'm not sure if it's on his website or not but here's a discussion we had about it in the ngs:

http://groups.google.com/group/micr...5/2486affc70838f75?lnk=st&q=#2486affc70838f75