Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Row Autofit problem Excel 2003 | Excel Discussion (Misc queries) | |||
Dynamic Range Problem | Excel Discussion (Misc queries) | |||
Baffling formula problem | Excel Discussion (Misc queries) | |||
How can I find the common names in two columns of names? | Excel Discussion (Misc queries) | |||
Worksheet copy problem - local names | Excel Discussion (Misc queries) |