Excel 2007 : Fill error when referencing another sheet in same workbook

Hello!

I am trying to resolve an error in a formula when trying to fill the formula to cells beneath it. The formula itself works just fine but when I try to fill it down I get the #REF! error in place of the reference to the other sheet I am referencing. I know I'm missing something small here.

I have an Excel workbook with only 2 sheets. One is "Data" and the other is "Tracker." I am entering this formula on the Tracker sheet referencing the Data sheet. Here is my formula:

=COUNTIFS(Data!N2:N1048576, $B4, Data!M2:M1048576, $N$2).

That formula works fine to do what I need it to do. But when I try to fill it down I get this:

=COUNTIFS(Data!#REF!, $B6, Data!#REF!, $N$2)

I want it to always reference the same columns on the Data sheet and the only thing that should change in each filled row is the B column/cell reference, which is working.

I'm sorry I can't share the actual file because it has sensitive data on it.

What am I missing?

Thank you!

This is because N1048576 is the last row of the spreadsheet. When you fill down, you're asking Excel to reference beyond the limit of it's environment. Make it something smaller such as N65536 which is the limit of the row range previous versions of Excel.

Thank you so much tlafferty!! My only issue now is that when I fill it down it is changing the N2:N65536 to N3:65537, N4:65538 etc...

make it $N$2:$N$65536 (the $ locks the reference in so it won't fill).