Dynamic Names can solve my problem?
 

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?

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")))

--
Regards
Frank Kabel
Frankfurt, Germany

shmurphing wrote:
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?

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

"Frank Kabel" wrote:

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")))

--
Regards
Frank Kabel
Frankfurt, Germany

shmurphing wrote:
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?

Hi
what exact formula have you used?

--
Regards
Frank Kabel
Frankfurt, Germany

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

"Frank Kabel" wrote:

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")))

--
Regards
Frank Kabel
Frankfurt, Germany

shmurphing wrote:
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?

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?