Dynamic Names can solve my problem?

[shmurphing]

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 Name>Define, 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?

 

[Frank Kabel]

Hi
try:
=SUMPRODUCT(--(INDIRECT("'Wpg #s
data'!C6:C590")="Incident"),--(INDIRECT("'Wpg #s
data'!J6:J590")="No Impact"),--(INDIRECT("'Wpg #s data'!F6:F590<C2")))

 

[shmurphing]

I am getting a Ref# error and everything looks okay. Thoughts?

 

[Frank Kabel]

Hi
what exact formula have you used?

 

[Daniel.M]

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.