Named Formula reference problem

[robert.hatcher]

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

 

[Bob Phillips]

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)

 

[vezerid]

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

 

[robert.hatcher]

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]

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

 

[Bob Phillips]

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]

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?

 

[vezerid]

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

 

[RagDyer]

Is this perhaps pertinent to your conversation?

http://tinyurl.com/yc9pd4

 

[Bob Phillips]

No.

Is this perhaps pertinent to your conversation?

http://tinyurl.com/yc9pd4

--
Regards,

RD

-------------------------------------------------------------------------- -
Please keep all correspondence within the NewsGroup, so all may benefit !
-------------------------------------------------------------------------- -

 

[RagDyeR]

Oh well!

No.

Is this perhaps pertinent to your conversation?

http://tinyurl.com/yc9pd4

--
Regards,

RD

-------------------------------------------------------------------------- -
Please keep all correspondence within the NewsGroup, so all may benefit !
-------------------------------------------------------------------------- -