problem with "named" cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Carl Witthoft - 01 Oct 2007 22:13 GMT

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

Signature

Team EM to the rescue! mailto:carl@Team-EM.com http://www.team-em.com

Reply to this Message

JE McGimpsey - 02 Oct 2007 04:33 GMT

> 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 .
>
[quoted text clipped - 11 lines]
> 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?

Reply to this Message

Carl Witthoft - 02 Oct 2007 22:14 GMT

> > 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
[quoted text clipped - 3 lines]
>
> 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

> > I created a Name that refers to a pair of cells, e.g. A1:A2 .
> >
[quoted text clipped - 13 lines]
>
> 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

Signature

Team EM to the rescue! mailto:carl@Team-EM.com http://www.team-em.com

Reply to this Message

JE McGimpsey - 02 Oct 2007 22:51 GMT

> > > I started writing a formula and click-dragged those two cells, and sure
> > > enough the formula Excel created was
[quoted text clipped - 17 lines]
> =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)

Reply to this Message

Carl Witthoft - 03 Oct 2007 22:52 GMT

> > > > I started writing a formula and click-dragged those two cells, and sure
> > > > enough the formula Excel created was
[quoted text clipped - 32 lines]
>
> =SUM(3, NameRef)

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.

Signature

Team EM to the rescue! mailto:carl@Team-EM.com http://www.team-em.com

Reply to this Message

Bob Greenblatt - 04 Oct 2007 12:51 GMT

On 10/3/07 5:52 PM, in article
carl-747FDB.17524703102007@comcast.dca.giganews.com, "Carl Witthoft"
<carl@witthoft.com> wrote:

>> > In article <carl-837EE8.17145802102007@comcast.dca.giganews.com>,
>> > Carl Witthoft <carl@witthoft.com> wrote:
>> >
>>>>> > > > > 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"

>>>>> > > > > 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

>>>>> > > > > #Value! error. However the bottom couple of cells traced back
>>>>> only to
[quoted text clipped - 27 lines]
> 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.

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.

Signature

Bob Greenblatt [MVP], Macintosh
bobgreenblattATmsnDOTcom

Reply to this Message

Carl Witthoft - 05 Oct 2007 01:58 GMT

=SUM(3, NameRef)

> > Well, I recognize that. The bug I'm claiming is that the identical
> > formula, in different cells, causes Excel to point to different
[quoted text clipped - 15 lines]
> 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?

Signature

Team EM to the rescue! mailto:carl@Team-EM.com http://www.team-em.com

Reply to this Message

Bob Greenblatt - 05 Oct 2007 13:39 GMT

> 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?

Signature

Bob Greenblatt [MVP], Macintosh
bobgreenblattATmsnDOTcom

Reply to this Message

Carl Witthoft - 05 Oct 2007 22:38 GMT

> > 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
[quoted text clipped - 13 lines]
> 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

Signature

Team EM to the rescue! mailto:carl@Team-EM.com http://www.team-em.com

Reply to this Message

CyberTaz - 06 Oct 2007 15:24 GMT

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

On 10/5/07 5:38 PM, in article
carl-D842EC.17381205102007@comcast.dca.giganews.com, "Carl Witthoft"
<carl@witthoft.com> wrote:

>>> 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
[quoted text clipped - 27 lines]
>
> Carl

Reply to this Message

Carl Witthoft - 07 Oct 2007 02:03 GMT

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 :-)

> Hi Carl -
>
[quoted text clipped - 52 lines]
> >
> > Carl

Signature

Team EM to the rescue! mailto:carl@Team-EM.com http://www.team-em.com

Reply to this Message

CyberTaz - 07 Oct 2007 14:06 GMT

On 10/6/07 9:03 PM, in article
carl-EB9800.21030706102007@comcast.dca.giganews.com, "Carl Witthoft"
<carl@witthoft.com> wrote:

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

Reply to this Message

Mac Forum / Applications / Excel / October 2007

problem with "named" cells