problem with "named" cells

[Carl Witthoft]

Here's something I never expected:

I have a spreadsheet which, for some reason in the past (before I got
it), had a number of "Names" applied to cells. Each name refers to a
single cell. That's fine. The first problem I found: When I'm writing
a formula in some other cell, I tend to use the "click in cell" method
to select referenced cells. That is, I type

= 3+

Then I click in, say cell A1 and the formula becomes

=3+A1

But in this workbook, there is a Name referencing A1, so when I click
(while writing the formula), Excel 'helpfully' inserts the name
instead, giving me

= 3+Item

as the formula. The problem with this is that I'm now stuck with an
absolute reference, rather than a relative one.

So the first question is: is there a Preference or something I can set
to force Excel to stick "A1" in the formula regardless of the current
set of Named cells?

Next bug: I created a Name that refers to a pair of cells, e.g.
A1:A2 .

I started writing a formula and click-dragged those two cells, and sure
enough the formula Excel created was

=3+NameRef (where "NameRef" is the name referring to A1:A2).

Now here's the strange thing: I did a five or 6-cell "Fill Down" of
that formula. All cells in the column read the same, i.e. =3+NameRef,

but Excel did NOT treat them the same. I used the TracePrecedents tool,
and found that most of the cells traced back to A1:A2, and gave me a
#Value! error. However the bottom couple of cells traced back only to
A1, and gave me the result of adding 3 to the contents of A1.
So, what the heck is going on and what should I do about it?

This was with Office2004 on an Intel iMac Core Duo, all latest Office
updates applied.

Many thanks for your help and ideas.
Carl

 

[JE McGimpsey]

So the first question is: is there a Preference or something I can set
to force Excel to stick "A1" in the formula regardless of the current
set of Named cells?

Preference/Calculation, uncheck the "Accept labels in formulas" checkbox.

Next bug:

Note that the above isn't a bug, it works by design.

I created a Name that refers to a pair of cells, e.g. A1:A2 .

I started writing a formula and click-dragged those two cells, and sure
enough the formula Excel created was

=3+NameRef (where "NameRef" is the name referring to A1:A2).

Now here's the strange thing: I did a five or 6-cell "Fill Down" of
that formula. All cells in the column read the same, i.e. =3+NameRef,

but Excel did NOT treat them the same. I used the TracePrecedents tool,
and found that most of the cells traced back to A1:A2, and gave me a
#Value! error. However the bottom couple of cells traced back only to
A1, and gave me the result of adding 3 to the contents of A1.
So, what the heck is going on and what should I do about it?

What is the exact definition of NameRef in Insert/Name/Define?

 

[Carl Witthoft]

Preference/Calculation, uncheck the "Accept labels in formulas" checkbox.


Note that the above isn't a bug, it works by design.

Fair enough, but I sure couldn't find a discussion of this via the Help
menus

What is the exact definition of NameRef in Insert/Name/Define?

Not sure what you mean: I just clicked in the "refers to" box and then
click-dragged the two cells of interest. The formula is

=Sheet1!$A$1:$A$2
(or whatever two vertically contiguous cells you happen to select)

Most important: thanks for the info!

Carl

 

[JE McGimpsey]

What is the exact definition of NameRef in Insert/Name/Define?

Not sure what you mean: I just clicked in the "refers to" box and then
click-dragged the two cells of interest. The formula is

=Sheet1!$A$1:$A$2
(or whatever two vertically contiguous cells you happen to select)[/QUOTE]

OK - the problem is your syntax. What do you expect the equivalent:

=3 + Sheet1!$A1:$A2

to return? XL's returning an array, but only when the formula exists in
the same row as the reference (if you array enter the formula, the
addition of 3 and the first value in the array WILL be returned to each
cell).

I suspect you want something like

=SUM(3, NameRef)

 

[Carl Witthoft]

Not sure what you mean: I just clicked in the "refers to" box and then
click-dragged the two cells of interest. The formula is

=Sheet1!$A$1:$A$2
(or whatever two vertically contiguous cells you happen to select)

OK - the problem is your syntax. What do you expect the equivalent:

=3 + Sheet1!$A1:$A2

to return? XL's returning an array, but only when the formula exists in
the same row as the reference (if you array enter the formula, the
addition of 3 and the first value in the array WILL be returned to each
cell).

I suspect you want something like

=SUM(3, NameRef)[/QUOTE]

Well, I recognize that. The bug I'm claiming is that the identical
formula, in different cells, causes Excel to point to different
precedent cells. In particular, I get #VALUE, as I would expect (and
you pointed out) due to the syntax mess. I want to know why a couple of
the cells decided to ignore the full referenced area and only refer to
one cell within the Named region.

 

[Bob Greenblatt]

Well, I recognize that. The bug I'm claiming is that the identical
formula, in different cells, causes Excel to point to different
precedent cells. In particular, I get #VALUE, as I would expect (and
you pointed out) due to the syntax mess. I want to know why a couple of
the cells decided to ignore the full referenced area and only refer to[/QUOTE]
one cell within the Named region.

PMFJI,

But you are not seeing a bug. It is EXACTLY as designed for a very good
purpose. A defined name does not have to be an absolute reference. It can
also be a relative reference. This is done a LOT to make worksheet formulas
much more readable. For example you could define a name (like TOTAL) to
refer to all the sells to the left of it. Then the formula =sum(total) in a
column will sum all the cells to its left. Based on my recollection of the
early parts of this thread, the name you defined was a relative reference,
and it caused you trouble because you assumed it was absolute. What you are
calling a bug, or inconvenience is a major power feature strongpoint of
Excel.

 

[Carl Witthoft]

=SUM(3, NameRef)

one cell within the Named region.

PMFJI,

But you are not seeing a bug. It is EXACTLY as designed for a very good
purpose. A defined name does not have to be an absolute reference. It can
also be a relative reference. This is done a LOT to make worksheet formulas
much more readable. For example you could define a name (like TOTAL) to
refer to all the sells to the left of it. Then the formula =sum(total) in a
column will sum all the cells to its left. Based on my recollection of the
early parts of this thread, the name you defined was a relative reference,
and it caused you trouble because you assumed it was absolute. What you are
calling a bug, or inconvenience is a major power feature strongpoint of
Excel.

Sorry, but I can't comprehend your response. The defined name in my
case is an absolute reference. I know perfectly well I could redefine
it. That's not the question. The question is: why, when the Name is an
absolute reference, do different cells containing identical formulas
process the Name reference differently?

 

[Bob Greenblatt]

Sorry, but I can't comprehend your response. The defined name in my
case is an absolute reference. I know perfectly well I could redefine
it. That's not the question. The question is: why, when the Name is an
absolute reference, do different cells containing identical formulas

process the Name reference differently?

Maybe I need an update on what you are seeing. In a previous post, you said
the name definition was:
=3 + Sheet1!$A1:$A2

This is an absolute reference for column only, and a relative reference by
row. Although the text of the formula in the cell may be identical, the
resolution of the name may indeed be different depending on the name¹s
definition.

Again please, what is the exact formula, the definition of the name, and
what are you seeing that you think is wrong?

 

[Carl Witthoft]

Sorry, but I can't comprehend your response. The defined name in my
case is an absolute reference. I know perfectly well I could redefine
it. That's not the question. The question is: why, when the Name is an
absolute reference, do different cells containing identical formulas

process the Name reference differently?

Maybe I need an update on what you are seeing. In a previous post, you said
the name definition was:
=3 + Sheet1!$A1:$A2

This is an absolute reference for column only, and a relative reference by
row. Although the text of the formula in the cell may be identical, the
resolution of the name may indeed be different depending on the name¹s
definition.

Again please, what is the exact formula, the definition of the name, and
what are you seeing that you think is wrong?[/QUOTE]

My apologies: I meant to write sheet1!$A$1:$A$2

However, I tried defining both as A$1:A$2 and fully absolute, and no
difference when I took my formula "=3+Name" and did a Fill Down.

I look at the Precedents and, with ExcelX, when the formula is in the
same row as the name ref (in this example if the formula is in row 1 or
row 2), it takes the value in just one of the referenced cells and gives
me a valid result. Anywhere else in the spreadsheet and I get #Value.

I will email you a small workbook with these examples in it.

Carl

 

[CyberTaz]

Hi Carl -

I've been watching this thread with interest but have held back because the
guys responding to you are far more experienced than I, but I think I may be
able to offer an explanation that will help

John hit the nail on the head with his second reply - the problem is with
the *syntax* of your formula. The way it's expressed it is basically saying
"add 3 to the value in the first cell in this range" on the row where you
entered it - in this example A1. Copying down to the next row says "add 3 to
the value in the *second* cell in this range" which is A2.

When you copy it down to the next row it's saying "add 3 to the value in the
third cell in this range" which 'would be' A3, but there *isn't* a third
cell in the range. That's why you get the error starting in the third row
and for each row thereafter. Make sense?

HTH |:>)
Bob Jones
[MVP] Office:Mac

process the Name reference differently?

Maybe I need an update on what you are seeing. In a previous post, you said
the name definition was:
=3 + Sheet1!$A1:$A2

This is an absolute reference for column only, and a relative reference by
row. Although the text of the formula in the cell may be identical, the
resolution of the name may indeed be different depending on the name¼s
definition.

Again please, what is the exact formula, the definition of the name, and
what are you seeing that you think is wrong?

My apologies: I meant to write sheet1!$A$1:$A$2

However, I tried defining both as A$1:A$2 and fully absolute, and no
difference when I took my formula "=3+Name" and did a Fill Down.

I look at the Precedents and, with ExcelX, when the formula is in the
same row as the name ref (in this example if the formula is in row 1 or
row 2), it takes the value in just one of the referenced cells and gives
me a valid result. Anywhere else in the spreadsheet and I get #Value.

I will email you a small workbook with these examples in it.

Carl[/QUOTE]

 

[Carl Witthoft]

I think that's probably what's happening -- and I would like publically
to thank Bob G for his email help.

To make it clear to all: I did not intend to use a formula as whacked
as this. I did so purely to see how Excel would respond to a mistake
like this. IMHO the way Excel "decides" to grab a value is not good --
I'd rather get an error message regardless of the relative location of
the formula cell and the referenced area (since the Name ref contains an
absolute reference), but I'm also painfully aware from my own
job-related coding that it's next to impossible to predict every dumb
thing a user will try to do with your code

 

[CyberTaz]

On 10/6/07 9:03 PM, in article
(e-mail address removed), "Carl Witthoft"

it's next to impossible to predict every dumb
thing a user will try to do with your code

....especially when what the user does "by mistake" results in a viable -
though misstated - expression. However, the Trace Error button's Help on
this Error option does include this sort of problem in the explanations
offered - "Supplying a range to an operator or a function...".

Regards |:>)
Bob Jones
[MVP] Office:Mac