| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Named Formula reference problem
I have a Named Formula:
=OFFSET(Htestc!BINFilStart,0,0,COUNTA(BINFilStart: A133),1) once the formula is entered as a name the it changes to: =OFFSET(Htestc!BINFilStart,0,0,COUNTA(Htestc!BINFi lStart:Htestc!IV132),1) "something" adds the sheet name, Htestc, to all references which doesnt hurt but also the reference A133 gets change to some random value, in this case "IV132" what causes this, or more importantly, what should I do about it. Thanks Robert |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Named Formula reference problem
Use
=OFFSET(Htestc!BINFilStart,0,0,COUNTA(BINFilStart: $A$133),1) excel adds the sheetname as the range could be referred to from another sheet. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message ups.com... I have a Named Formula: =OFFSET(Htestc!BINFilStart,0,0,COUNTA(BINFilStart: A133),1) once the formula is entered as a name the it changes to: =OFFSET(Htestc!BINFilStart,0,0,COUNTA(Htestc!BINFi lStart:Htestc!IV132),1) "something" adds the sheet name, Htestc, to all references which doesnt hurt but also the reference A133 gets change to some random value, in this case "IV132" what causes this, or more importantly, what should I do about it. Thanks Robert |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Named Formula reference problem
Try COUNTA(BINFilStart:INDIRECT("A133")) in your name definition. This
is guaranteed not to change the end-range. Safer than COUNTA(BINFilStart:$A$133), in case a row is inserted, but then again you might want the row to change as well. On the other hand I cannot help but wonder whence IV and whence 132. The latter I can understand: if you defined your formula when you were in, say, J7, the address is relative. Now if you use the formula in row 6, 133-132. The IV seems like you moved one column to the left and A "wrapped" to IV. Is this the case by any chance? Does this help? Kostis Vezerides wrote: I have a Named Formula: =OFFSET(Htestc!BINFilStart,0,0,COUNTA(BINFilStart: A133),1) once the formula is entered as a name the it changes to: =OFFSET(Htestc!BINFilStart,0,0,COUNTA(Htestc!BINFi lStart:Htestc!IV132),1) "something" adds the sheet name, Htestc, to all references which doesnt hurt but also the reference A133 gets change to some random value, in this case "IV132" what causes this, or more importantly, what should I do about it. Thanks Robert |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Named Formula reference problem
Thanks Kostis, that worked perfect. Im not sure about how I enterted
it, usualy I was several rows to the right whne I entered the named formula... thanks Again, I was realy racking my brain on this... vezerid wrote: Try COUNTA(BINFilStart:INDIRECT("A133")) in your name definition. This is guaranteed not to change the end-range. Safer than COUNTA(BINFilStart:$A$133), in case a row is inserted, but then again you might want the row to change as well. On the other hand I cannot help but wonder whence IV and whence 132. The latter I can understand: if you defined your formula when you were in, say, J7, the address is relative. Now if you use the formula in row 6, 133-132. The IV seems like you moved one column to the left and A "wrapped" to IV. Is this the case by any chance? Does this help? Kostis Vezerides wrote: I have a Named Formula: =OFFSET(Htestc!BINFilStart,0,0,COUNTA(BINFilStart: A133),1) once the formula is entered as a name the it changes to: =OFFSET(Htestc!BINFilStart,0,0,COUNTA(Htestc!BINFi lStart:Htestc!IV132),1) "something" adds the sheet name, Htestc, to all references which doesnt hurt but also the reference A133 gets change to some random value, in this case "IV132" what causes this, or more importantly, what should I do about it. Thanks Robert |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Named Formula reference problem
Hi Kostis,
I would say it is a feature on the following basis. If you are using relative referencing presumably it is deliberate (this thread is the exception to the rule <g), and as such, the last thing you want is for it to error in any column/row. The code should handle any situation it considers an error, not have the system decide for it. So a feature IMO, but not one that I have ever utilised I must admit. It also works on rows, 1 cycles around to 65536. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "vezerid" wrote in message oups.com... Glad it worked, thanks for the feedback. I tried a similar test and I reproduced the behavior. I defined a name test in B1 =A1. Then in A1 I entered =test and it was pointing to IV1. Bug or feature, I wonder. Bob, since you are following the thread, any ideas? Bug or feature? Regards, Kostis wrote: Thanks Kostis, that worked perfect. Im not sure about how I enterted it, usualy I was several rows to the right whne I entered the named formula... thanks Again, I was realy racking my brain on this... vezerid wrote: Try COUNTA(BINFilStart:INDIRECT("A133")) in your name definition. This is guaranteed not to change the end-range. Safer than COUNTA(BINFilStart:$A$133), in case a row is inserted, but then again you might want the row to change as well. On the other hand I cannot help but wonder whence IV and whence 132. The latter I can understand: if you defined your formula when you were in, say, J7, the address is relative. Now if you use the formula in row 6, 133-132. The IV seems like you moved one column to the left and A "wrapped" to IV. Is this the case by any chance? Does this help? Kostis Vezerides wrote: I have a Named Formula: =OFFSET(Htestc!BINFilStart,0,0,COUNTA(BINFilStart: A133),1) once the formula is entered as a name the it changes to: =OFFSET(Htestc!BINFilStart,0,0,COUNTA(Htestc!BINFi lStart:Htestc!IV132),1) "something" adds the sheet name, Htestc, to all references which doesnt hurt but also the reference A133 gets change to some random value, in this case "IV132" what causes this, or more importantly, what should I do about it. Thanks Robert |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Named Formula reference problem
Bob, you are intriguing me now <bg. Let us say I want to define a
named formula with relative references. As I understand it, the name is simply a representative of a calculation routine, which in turn is defined the formula. This formula, if unnamed, would cause the #REF! error if copied out of bounds. Why should we not expect a similar behavior by any formula using the named formula inside it? Can you also explain the following? The code should handle any situation it considers an error, not have the system decide for it. Are you referring to error handling at the VBA level of implementation? To the formula? You mean a formula which will first test its relative references' positions and decide to return an error value itself? Bob Phillips wrote: Hi Kostis, I would say it is a feature on the following basis. If you are using relative referencing presumably it is deliberate (this thread is the exception to the rule <g), and as such, the last thing you want is for it to error in any column/row. The code should handle any situation it considers an error, not have the system decide for it. So a feature IMO, but not one that I have ever utilised I must admit. It also works on rows, 1 cycles around to 65536. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "vezerid" wrote in message oups.com... Glad it worked, thanks for the feedback. I tried a similar test and I reproduced the behavior. I defined a name test in B1 =A1. Then in A1 I entered =test and it was pointing to IV1. Bug or feature, I wonder. Bob, since you are following the thread, any ideas? Bug or feature? Regards, Kostis |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Named Formula reference problem
Bob, I apologize beforehand, in case you reply I will not see your
message until tomorrow, they are kicking me out of the building and I gotta go. Have a good night. Kostis vezerid wrote: Bob, you are intriguing me now <bg. Let us say I want to define a named formula with relative references. As I understand it, the name is simply a representative of a calculation routine, which in turn is defined the formula. This formula, if unnamed, would cause the #REF! error if copied out of bounds. Why should we not expect a similar behavior by any formula using the named formula inside it? Can you also explain the following? The code should handle any situation it considers an error, not have the system decide for it. Are you referring to error handling at the VBA level of implementation? To the formula? You mean a formula which will first test its relative references' positions and decide to return an error value itself? Bob Phillips wrote: Hi Kostis, I would say it is a feature on the following basis. If you are using relative referencing presumably it is deliberate (this thread is the exception to the rule <g), and as such, the last thing you want is for it to error in any column/row. The code should handle any situation it considers an error, not have the system decide for it. So a feature IMO, but not one that I have ever utilised I must admit. It also works on rows, 1 cycles around to 65536. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "vezerid" wrote in message oups.com... Glad it worked, thanks for the feedback. I tried a similar test and I reproduced the behavior. I defined a name test in B1 =A1. Then in A1 I entered =test and it was pointing to IV1. Bug or feature, I wonder. Bob, since you are following the thread, any ideas? Bug or feature? Regards, Kostis |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Named Formula reference problem
Is this perhaps pertinent to your conversation?
http://tinyurl.com/yc9pd4 -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "vezerid" wrote in message oups.com... Bob, you are intriguing me now <bg. Let us say I want to define a named formula with relative references. As I understand it, the name is simply a representative of a calculation routine, which in turn is defined the formula. This formula, if unnamed, would cause the #REF! error if copied out of bounds. Why should we not expect a similar behavior by any formula using the named formula inside it? Can you also explain the following? The code should handle any situation it considers an error, not have the system decide for it. Are you referring to error handling at the VBA level of implementation? To the formula? You mean a formula which will first test its relative references' positions and decide to return an error value itself? Bob Phillips wrote: Hi Kostis, I would say it is a feature on the following basis. If you are using relative referencing presumably it is deliberate (this thread is the exception to the rule <g), and as such, the last thing you want is for it to error in any column/row. The code should handle any situation it considers an error, not have the system decide for it. So a feature IMO, but not one that I have ever utilised I must admit. It also works on rows, 1 cycles around to 65536. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "vezerid" wrote in message oups.com... Glad it worked, thanks for the feedback. I tried a similar test and I reproduced the behavior. I defined a name test in B1 =A1. Then in A1 I entered =test and it was pointing to IV1. Bug or feature, I wonder. Bob, since you are following the thread, any ideas? Bug or feature? Regards, Kostis |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Named Formula reference problem
No.
"RagDyer" wrote in message ... Is this perhaps pertinent to your conversation? http://tinyurl.com/yc9pd4 -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "vezerid" wrote in message oups.com... Bob, you are intriguing me now <bg. Let us say I want to define a named formula with relative references. As I understand it, the name is simply a representative of a calculation routine, which in turn is defined the formula. This formula, if unnamed, would cause the #REF! error if copied out of bounds. Why should we not expect a similar behavior by any formula using the named formula inside it? Can you also explain the following? The code should handle any situation it considers an error, not have the system decide for it. Are you referring to error handling at the VBA level of implementation? To the formula? You mean a formula which will first test its relative references' positions and decide to return an error value itself? Bob Phillips wrote: Hi Kostis, I would say it is a feature on the following basis. If you are using relative referencing presumably it is deliberate (this thread is the exception to the rule <g), and as such, the last thing you want is for it to error in any column/row. The code should handle any situation it considers an error, not have the system decide for it. So a feature IMO, but not one that I have ever utilised I must admit. It also works on rows, 1 cycles around to 65536. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "vezerid" wrote in message oups.com... Glad it worked, thanks for the feedback. I tried a similar test and I reproduced the behavior. I defined a name test in B1 =A1. Then in A1 I entered =test and it was pointing to IV1. Bug or feature, I wonder. Bob, since you are following the thread, any ideas? Bug or feature? Regards, Kostis |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Named Formula reference problem
Oh well!
"Bob Phillips" wrote in message ... No. "RagDyer" wrote in message ... Is this perhaps pertinent to your conversation? http://tinyurl.com/yc9pd4 -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "vezerid" wrote in message oups.com... Bob, you are intriguing me now <bg. Let us say I want to define a named formula with relative references. As I understand it, the name is simply a representative of a calculation routine, which in turn is defined the formula. This formula, if unnamed, would cause the #REF! error if copied out of bounds. Why should we not expect a similar behavior by any formula using the named formula inside it? Can you also explain the following? The code should handle any situation it considers an error, not have the system decide for it. Are you referring to error handling at the VBA level of implementation? To the formula? You mean a formula which will first test its relative references' positions and decide to return an error value itself? Bob Phillips wrote: Hi Kostis, I would say it is a feature on the following basis. If you are using relative referencing presumably it is deliberate (this thread is the exception to the rule <g), and as such, the last thing you want is for it to error in any column/row. The code should handle any situation it considers an error, not have the system decide for it. So a feature IMO, but not one that I have ever utilised I must admit. It also works on rows, 1 cycles around to 65536. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "vezerid" wrote in message oups.com... Glad it worked, thanks for the feedback. I tried a similar test and I reproduced the behavior. I defined a name test in B1 =A1. Then in A1 I entered =test and it was pointing to IV1. Bug or feature, I wonder. Bob, since you are following the thread, any ideas? Bug or feature? Regards, Kostis |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Is it possible? | Excel Worksheet Functions | |||
| Reusing formula | Excel Discussion (Misc queries) | |||
| Copy a relative reference formula from one sheet to another. | Excel Discussion (Misc queries) | |||
| problem with cell reference in =sum(offset(cell reference,x,y,z,a)). Want cell ref to be variable. | Excel Worksheet Functions | |||
| Problem with =sum(offset(cell reference,w,x,y,z). I want cell reference to be variable | Excel Worksheet Functions | |||
Linear Mode
