Defined Name problem

  • Thread starter Thread starter Tom HayakAwa
  • Start date Start date
T

Tom HayakAwa

Thanks for looking at this post. I noticed that if I set
up a defined name for a range on one worksheet (say 'Sheet
1'!A1:D50) and call it JanData03, whenever I use that
defined name on another worksheet in the same range, I get
whatever value is present in that cell on Sheet 1.

For example, if I type in JanData03 in worksheeet Sheet 3,
cell L3, I get #VALUE, which I would expect. However, if
I type in JanData03 in worksheet Sheet 3, cell A3, I get
the value of cell A3 on Worksheet Sheet 1.

Could someone please tell me if this is a "feature" or a
bug? It is a real problem for me when I try to use the
ERROR.TYPE function in the range of the defined name,
whatever worksheet I'm on. (I realize I could just make
sure I never mention the defined name in the columns and
rows the range sits in, but that's a bit restricting).
Thanks for any help and guidance.
 
Hi Tom,

It is usually better to to use absolute defined names;
'Sheet1'!$A$1:$D$50 rather than relative names.

Relative names always refer to a row and column offset from the cell that
the name is being used in, and so the cell referred to keeps changing.

If you use defined names a lot you might consider using Name Manager
(written by myself and Jan Karel Pieterse) which you can download from
http://www.DecisionModels.com/downloads.htm

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm
 
Charles,

Thanks for the reply. Actually, I am using an absolute.
I mistakenly left out the $'s. At least, that's what
shows when I look in the Insert>Name>Define box. Is there
something else I need to do to make sure this is an
absolute defined range?

P.S. I had seen your Names Manager and was considering
using it. I guess I will look at it again.
 
Tom HayakAwa said:
Thanks for looking at this post. I noticed that if I set
up a defined name for a range on one worksheet (say 'Sheet
1'!A1:D50) and call it JanData03, whenever I use that
defined name on another worksheet in the same range, I get
whatever value is present in that cell on Sheet 1.

For example, if I type in JanData03 in worksheeet Sheet 3,
cell L3, I get #VALUE, which I would expect. However, if
I type in JanData03 in worksheet Sheet 3, cell A3, I get
the value of cell A3 on Worksheet Sheet 1.

Could someone please tell me if this is a "feature" or a
bug? It is a real problem for me when I try to use the
ERROR.TYPE function in the range of the defined name,
whatever worksheet I'm on. (I realize I could just make
sure I never mention the defined name in the columns and
rows the range sits in, but that's a bit restricting).
Thanks for any help and guidance.
Click to expand...

I assume it was intended thus, which I suppose makes it a feature. You can
defines names as either worksheet-level or workbook-level, depending on
whether or not you include the sheet name in the definition. It is perfectly
possible, for example, to have in a workbook two or more names that are the
same, as long as they are worksheet names. Then each applies within its own
worksheet. So a formula containing the name can look the same in two
different worksheets (and thus be copied from one to the other), but each
will refer to its own 'locally-named' cell. (This is the nearest that
worksheets names get to being relative.) However, unless you specifically
want this feature, it's best to stick with workbook-level names.
 
Paul,

Man, you guys are fast.... Actually, I left out the $'s
by mistake from the range when I wrote the original post.
This is a workbook-level defined name (ie, JanData03
= 'Sheet 1'!$A$1:$D$50). Shouldn't this refer only to
that specifc range on Sheet 1 and not on similar ranges on
other worksheets?
 
Yes. Whichever sheet you are on, this name will refer to that specific range
on Sheet1. Are you saying that's not what is happening? If so, have you
checked the name definition using
Insert > Name > Define
and then selecting the name in the list of defined names? In the "Referes
to" box, do you have
='Sheet 1'!$A$1:$D$50
 
Paul,

Yes, that's exactly what's there: ='Sheet 1'!$A$1:$D$50

If I type JanData03 into cell E1 on Sheet 2, I get #VALUE,
which I would expect. But if I type JanData03 into cell
A1 on Sheet 2, I get the value in cell 'Sheet 1'!A1. This
throws a monkey wrench into the formualas I was using,
which used ERROR.TYPE to let me put a zero in place of an
#NA or a #VALUE error (this is a spreadsheet updated
monthly, and I wanted to lay out the spreadsheet at the
beginning of the year without having to look at all the
#NA and #VALUE errors for monthly ranges for upcoming
months that I don't have data for yet).
 
Hi Tom,

Maybe you have got duplicate global-local names:

Try downloading Name manager and seeing what it tells you: it has some
clever error checking and its also much easier to see things like
duplicates.


regds
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm
 
Charles,

Actually, after you mentioned it in your reply this
morning I went ahead and did just that, the defined name
in question is global and there is no duplicate.
 
I did some playing around with a new worksheet. I entered
in 1 through 5 in cells B3 to F3, 5 through 10 in cells B4
to F4, a through f in cells B5 to F5 and f through j in
cells B6 to F6. Then I defined the name Test to 'Sheet 1'!
$B$3:$F$6. Then I switched over to Sheet 2 and entered
=Test into cells A2 to G7. The result was #VALUE in cells
A2 to G2, A3 to A6, G3 to G6, A7 to G7, and for cells B6
to F6 (on Sheet 2) I got the values for the corresponding
cells on Sheet 1. I also used Name Manager to confirm
Test was Global and not Local.

Then I went into Insert>Name>Define and changed Test from
referencing absolute to relative values and got
(predictably) all sorts of circular errors.

My question is now this: Is this a bug that should be
reported, or is there an explanation for this behavior,
which is inconsistent with the documentation?

Heck, maybe MS will make it a doc fix and change the
documentation to fit the behavior...:)
 
Hi Tom,

I finally see what you are doing:

your name is returning an array. Excel does not like returning an array to a
cell unless it has some means of deciding which element of the array to
return.

You will get the same behaviour if you enter =Sheet1!$b$3:$F$6 into a single
cell.

You get a sensible answer if you
- enter as an array formulae iinto multiple cells
- or use =Index(test,1,1)
- or use implicit indexing by placing the formula in cells b3:f6

etc.

you can see the result in Name manager if you select Test and click the
evaluate button.


--
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm
 
Charles,

Thanks for your answer. I think I understand - I am
setting up an array (by definition, since array is just a
range of values) and Excel is treating it as an array
even though I don't intend to. I guess I don't
understand why Excel only understands to return a non-
errored value when I call from a similar relative
location on another sheet.

Actually, I have already done a workaround to this
problem - I moved all the named ranges beyond any area
that it could be called from (ie, the ranges are all out
in Column Q and the actual calculations are done in
Columns A-R.

Is there any other way I can check for the existence of a
defined name and/or a value in that range and keep the
#NAME and the #VALUE errors from being returned to my
calculations if they didn't exist?

Again, thanks for straightening me out, and thanks for
the tip on your Named Manager utility - I've already
started using it.

Tom Hayakawa
 
I guess I don't
understand why Excel only understands to return a non-
errored value when I call from a similar relative
location on another sheet.
Click to expand...

It is Excels implicit intersection; it is returning the intersection of the
range and the column and row containing the formula that refers to the range
(in this case a name). You get #Value because you are referring to
intersections that dont exist
Is there any other way I can check for the existence of a
defined name and/or a value in that range and keep the
#NAME and the #VALUE errors from being returned to my
calculations if they didn't exist?
Click to expand...

I dont think I understand what you are trying to do:

- either define a separate name for each cell you want to refer to
- or define the name as a range and use INDEX or OFFSET to refer to each
individual element
- or reference the range name in an array formula
- or use SUM(name) for instance when you want the whole range
- or use implicit intersection to reference individual elements (but this
seems to be giving you a problem so dont use it!)

Decide which way you are going to refer to the name and then if you want to
handle misspelled names etc use ISERROR or ISERR etc as appropriate.

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm
 
Charles,

Thank you for all the time you're giving me on this
problem. I now understand why Excel returns a value based
on the intersection of the formula in relation to the data
it is calling.

As for what I'm trying to do - I'm trying to set up a
spreadsheet with 12 columns - one for each month, and a
whole bunch of rows, each row contains the monthly
information required from a different lab test. I want to
have everything in that worksheet laid out beforehand with
the formula in the January column looking for the data
from January, and so on for each month. Since, in
January, I have no data for February or beyond. I wanted
a nice, clean spreadsheet with blanks or zeroes for all
instances where a defined name, JanData04, has been
defined, but the test wasn't run that month, so VLOOKUP
returns a #VALUE error, or the defined name hasn't been
defined yet, say AprData04, and so a VLOOKUP returns a
#NAME error. INDEX and OFFSET could also be used, I just
chose to use VLOOKUP.

Anyway, that's my rationale. No need to reply this post.
Thanks again for all your help. I will definitely be
using this vehicle for asking questions in the future.

Tom Hayakawa
 
Back
Top