Hi,

You need to use ABSOLUTE references when defining Names if you don't want them
to offset as you move in the spreadsheet.

Make sure you have ABSOLUTE references in your name definitions :
Example:
Client_Impact =$J$1:$J$1000
Same idea even if you use OFFSET(), in the case of a dynamic definition.
Client_Impact =OFFSET($J$1,0,0,COUNT($J$1:$J$10000)-1,1)

Regards,

Daniel M.


"shmurphing" wrote in message
...
I did run into some difficulty with my formula's. They keep changing.

Let me give you an example.
Here is what I started with:
=SUMPRODUCT(--('Wpg #s data'!C6:C590="Incident"),--('Wpg #s
data'!J6:J590="No Impact"),--('Wpg #s data'!F6:F590<C2))
I have named C Category; J Client_Impact; F Occurred.

The new formula looks like:
=SUMPRODUCT(--(Category="Incident"),--(Client_Impact="No
Impact"),--(Occurred<C2))

But it continues to go out of whack in the same manner as the formulas in
the cells that are entered manually whenever I update the OBDC source data.

When I look at the NameDefine, it shows that each one changes. So if I am
sitting on cell C6, it shows all the nameranges as 6-10000. If I look at C7,
it shows the names ranges as 7-10001, and so on.

I need the sheetsheet to look at the same data, no matter which cell I am
on. Any idea how to accomplish that feat?