Summary sheet calc results change if rows added/deleted in detail spreadsheet

[Excel 2003] I have 2 spreadsheets: one to summarize data from a 2nd detail spreadsheet. I'm analyzing work order information for a service operation.

I'm using dynamic name ranges, as follows, for the detail:

WO_Num =OFFSET('WO Tracking Log'!$A6,0,0,COUNT('WO Tracking Log'!$A:$A),1)

Other detail data is defined as these examples show:

GM_X =OFFSET(WO_Num,0,8)
OpenDate =OFFSET(WO_Num,0,1)

All detail data begins in Row 6 in the detail spreadsheet.

In the Summary spreadsheet, it appears to make a difference where my calcs are located in order for my COUNTIF's to work correctly. As long as I keep my summary calc (to total the number of work orders in the detail) in Row 2 of the Summary, it works fine...but if EITHER I move my calc down a row OR if my detail drops down a row because a row was added above (where I have just header info), my summary totals change?!?! I don't understand.

Here are two examples of the calcs I'm using in the summary:

=COUNTIF(WO_Num,">0")
=COUNTIF(GM_X,"X")

Can someone tell me what is going on? What I'm doing wrong?

You should use an Absolute reference in your named range, see:

Please Login or Register  to view this content.

Could you post the RefersTo formula in question ?

The B2 should also be an absolute (I suspect) no doubt an oversight on my part... it's rare that you wouldn't use absolutes in this context (for the reasons you're now aware of).

Regards Named Ranges ... only when you want the range to be relative to your current position... nothing springs to mind immediately ... I don't really use named ranges myself all that much so someone else may be able to provide some examples where a relative range would prove useful (and practical over using a native formula with range referenced directly)