 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / October 2006Named 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!BINFilStart: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 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.  SignatureHTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) > I have a Named Formula: > =OFFSET(Htestc!BINFilStart,0,0,COUNTA(BINFilStart:A133),1) [quoted text clipped - 8 lines] > Thanks > Robert 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 > I have a Named Formula: > =OFFSET(Htestc!BINFilStart,0,0,COUNTA(BINFilStart:A133),1) [quoted text clipped - 8 lines] > Thanks > Robert 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... > Try COUNTA(BINFilStart:INDIRECT("A133")) in your name definition. This > is guaranteed not to change the end-range. Safer than [quoted text clipped - 22 lines] > > Thanks > > Robert 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 > 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 [quoted text clipped - 28 lines] > > > Thanks > > > Robert 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. SignatureHTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) > Glad it worked, thanks for the feedback. > [quoted text clipped - 29 lines] > > > > =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!BINFilStart: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 [quoted text clipped - 3 lines] > > > > Thanks > > > > Robert 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? > Hi Kostis, > [quoted text clipped - 24 lines] > > Regards, > > Kostis 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 > 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 [quoted text clipped - 39 lines] > > > Regards, > > > Kostis Is this perhaps pertinent to your conversation? http://tinyurl.com/yc9pd4 SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > 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 [quoted text clipped - 39 lines] >> > Regards, >> > Kostis No. > Is this perhaps pertinent to your conversation? > [quoted text clipped - 43 lines] > >> > Regards, > >> > Kostis Oh well! No. > Is this perhaps pertinent to your conversation? > [quoted text clipped - 22 lines] > >> 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. Soa [quoted text clipped - 12 lines] > >> > > >> > 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 to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.