Creating names
 

Hi

I was recently asked in a training class why a person
would use Insert Name Create and I did not know about
this. I usually show my students how to define names from
selected cells. Could you please give me an example of
when I might use Create and what does the dialog box mean
that pops up Top Row, Left Column etc..

Many thanks

Ann

See:

http://www.xldynamic.com/source/xld.Names.html

HTH
Jason
Atlanta, GA

-----Original Message-----
Hi

I was recently asked in a training class why a person
would use Insert Name Create and I did not know about
this. I usually show my students how to define names
from
selected cells. Could you please give me an example of
when I might use Create and what does the dialog box
mean
that pops up Top Row, Left Column etc..

Many thanks

Ann
.

Ann,

Many reasons

- maintainability - define a name of VAT with a refersto value of 17.5% and
a formula of =A1*VAT is self-explanatory
- reduce complexity of formulas that have repeating functions - create a
name a make it more obvious and also shorter (e.g.
=IF(ISNA(VLOOKUP(A1,$H$1:$J$10,2,False)), "",VLOOKUP(A1,$H$1:$J$10,2,False))
can be shortened to =IF(ISNA(the_rate),"",the_rate) where the_rate is a name
with a Refersto value of =VLOOKUP(A1,$H$1:$J$10,2,False)
- in some circumstances, such as conditional formatting or data validation
where you want to refer to data on an other worksheet, you can only do this
by defining a name.

They are great, encourage their use.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ann Shaw" wrote in message
...
Hi

I was recently asked in a training class why a person
would use Insert Name Create and I did not know about
this. I usually show my students how to define names from
selected cells. Could you please give me an example of
when I might use Create and what does the dialog box mean
that pops up Top Row, Left Column etc..

Many thanks

Ann

Insert Names is a fast and convenient way to assign defined names to cells
adjacent to labels, using those labels. An example makes this easier to
follow:

-Enter 3 names in cells A1:A3 (say Jones, Brown, Smith).
-Select the 6 cell range A1:B3.
-Open the Insert Names dialog (Ctrl-Shift-F3 is a shortcut).
-Select the Left Column option (it may be selected automatically).
-Click OK.

Cells B1:B3 will now have the names Jones, Brown and Smith.

Has you selected a range with more columns, like A1:D3, then the names would
have been assigned to the range B1:D1, B2:D2 and B3:D3.


--
Jim Rech
Excel MVP
"Ann Shaw" wrote in message
...
| Hi
|
| I was recently asked in a training class why a person
| would use Insert Name Create and I did not know about
| this. I usually show my students how to define names from
| selected cells. Could you please give me an example of
| when I might use Create and what does the dialog box mean
| that pops up Top Row, Left Column etc..
|
| Many thanks
|
| Ann

Jim,

That's a great tip, but (and this is not meant as criticism, just extra help
for Ann) from the tone of the OP I think Ann would benefit from some ideas
on how to deploy such names. Some occur to me, but as I presume you actually
use this technique I will leave it to you to

Regards

Bob


"Jim Rech" wrote in message
...
Insert Names is a fast and convenient way to assign defined names to cells
adjacent to labels, using those labels. An example makes this easier to
follow:

-Enter 3 names in cells A1:A3 (say Jones, Brown, Smith).
-Select the 6 cell range A1:B3.
-Open the Insert Names dialog (Ctrl-Shift-F3 is a shortcut).
-Select the Left Column option (it may be selected automatically).
-Click OK.

Cells B1:B3 will now have the names Jones, Brown and Smith.

Has you selected a range with more columns, like A1:D3, then the names
would
have been assigned to the range B1:D1, B2:D2 and B3:D3.


--
Jim Rech
Excel MVP
"Ann Shaw" wrote in message
...
| Hi
|
| I was recently asked in a training class why a person
| would use Insert Name Create and I did not know about
| this. I usually show my students how to define names from
| selected cells. Could you please give me an example of
| when I might use Create and what does the dialog box mean
| that pops up Top Row, Left Column etc..
|
| Many thanks
|
| Ann

When I read your response, Bob, I thought 'that's a great tip but not what
the OP was asking'.<g So I thought I'd answer her actual question which I
took to be "how do I use the Create Names dialog?", not "why should I use
names?".

If Ann comes back maybe she can tell us who was more perceptive this
morning...<g

--
Jim Rech
Excel MVP
"Bob Phillips" wrote in message
...
| Jim,
|
| That's a great tip, but (and this is not meant as criticism, just extra
help
| for Ann) from the tone of the OP I think Ann would benefit from some ideas
| on how to deploy such names. Some occur to me, but as I presume you
actually
| use this technique I will leave it to you to
|
| Regards
|
| Bob
|
|
| "Jim Rech" wrote in message
| ...
| Insert Names is a fast and convenient way to assign defined names to
cells
| adjacent to labels, using those labels. An example makes this easier to
| follow:
|
| -Enter 3 names in cells A1:A3 (say Jones, Brown, Smith).
| -Select the 6 cell range A1:B3.
| -Open the Insert Names dialog (Ctrl-Shift-F3 is a shortcut).
| -Select the Left Column option (it may be selected automatically).
| -Click OK.
|
| Cells B1:B3 will now have the names Jones, Brown and Smith.
|
| Has you selected a range with more columns, like A1:D3, then the names
| would
| have been assigned to the range B1:D1, B2:D2 and B3:D3.
|
|
| --
| Jim Rech
| Excel MVP
| "Ann Shaw" wrote in message
| ...
| | Hi
| |
| | I was recently asked in a training class why a person
| | would use Insert Name Create and I did not know about
| | this. I usually show my students how to define names from
| | selected cells. Could you please give me an example of
| | when I might use Create and what does the dialog box mean
| | that pops up Top Row, Left Column etc..
| |
| | Many thanks
| |
| | Ann
|
|
|
|

Hi Jim,

Funny how language is so multi-facetted isn't it :-). Makes being a
highly-paid lawyer easy :-(

Ann, we all wait with bated breath <g

Bob

"Jim Rech" wrote in message
...
When I read your response, Bob, I thought 'that's a great tip but not what
the OP was asking'.<g So I thought I'd answer her actual question which
I
took to be "how do I use the Create Names dialog?", not "why should I use
names?".

If Ann comes back maybe she can tell us who was more perceptive this
morning...<g

--
Jim Rech
Excel MVP
"Bob Phillips" wrote in message
...
| Jim,
|
| That's a great tip, but (and this is not meant as criticism, just extra
help
| for Ann) from the tone of the OP I think Ann would benefit from some
ideas
| on how to deploy such names. Some occur to me, but as I presume you
actually
| use this technique I will leave it to you to
|
| Regards
|
| Bob
|
|
| "Jim Rech" wrote in message
| ...
| Insert Names is a fast and convenient way to assign defined names to
cells
| adjacent to labels, using those labels. An example makes this easier
to
| follow:
|
| -Enter 3 names in cells A1:A3 (say Jones, Brown, Smith).
| -Select the 6 cell range A1:B3.
| -Open the Insert Names dialog (Ctrl-Shift-F3 is a shortcut).
| -Select the Left Column option (it may be selected automatically).
| -Click OK.
|
| Cells B1:B3 will now have the names Jones, Brown and Smith.
|
| Has you selected a range with more columns, like A1:D3, then the names
| would
| have been assigned to the range B1:D1, B2:D2 and B3:D3.
|
|
| --
| Jim Rech
| Excel MVP
| "Ann Shaw" wrote in message
| ...
| | Hi
| |
| | I was recently asked in a training class why a person
| | would use Insert Name Create and I did not know about
| | this. I usually show my students how to define names from
| | selected cells. Could you please give me an example of
| | when I might use Create and what does the dialog box mean
| | that pops up Top Row, Left Column etc..
| |
| | Many thanks
| |
| | Ann
|
|
|
|

Guys Guys Guys please - you both helped me a lot over here
in little olde Dublin, Ireland.

Much appreciated. Hope you got my email Bob - any help on
Macros would be great and thanks a million both Bob and
Jim!!!!

Kind regards

Ann
-----Original Message-----
Hi Jim,

Funny how language is so multi-facetted isn't it :-).
Makes being a
highly-paid lawyer easy :-(

Ann, we all wait with bated breath <g

Bob

"Jim Rech" wrote in message
. ..
When I read your response, Bob, I thought 'that's a
great tip but not what
the OP was asking'.<g So I thought I'd answer her
actual question which
I
took to be "how do I use the Create Names dialog?",
not "why should I use
names?".

If Ann comes back maybe she can tell us who was more
perceptive this
morning...<g

--
Jim Rech
Excel MVP
"Bob Phillips"
wrote in message
...
| Jim,
|
| That's a great tip, but (and this is not meant as
criticism, just extra
help
| for Ann) from the tone of the OP I think Ann would
benefit from some
ideas
| on how to deploy such names. Some occur to me, but as
I presume you
actually
| use this technique I will leave it to you to
|
| Regards
|
| Bob
|
|
| "Jim Rech" wrote in message
| ...
| Insert Names is a fast and convenient way to assign
defined names to
cells
| adjacent to labels, using those labels. An example
makes this easier
to
| follow:
|
| -Enter 3 names in cells A1:A3 (say Jones, Brown,
Smith).
| -Select the 6 cell range A1:B3.
| -Open the Insert Names dialog (Ctrl-Shift-F3 is a
shortcut).
| -Select the Left Column option (it may be selected
automatically).
| -Click OK.
|
| Cells B1:B3 will now have the names Jones, Brown
and Smith.
|
| Has you selected a range with more columns, like
A1:D3, then the names
| would
| have been assigned to the range B1:D1, B2:D2 and
B3:D3.
|
|
| --
| Jim Rech
| Excel MVP
| "Ann Shaw" wrote
in message
| ...
| | Hi
| |
| | I was recently asked in a training class why a
person
| | would use Insert Name Create and I did not know
about
| | this. I usually show my students how to define
names from
| | selected cells. Could you please give me an
example of
| | when I might use Create and what does the dialog
box mean
| | that pops up Top Row, Left Column etc..
| |
| | Many thanks
| |
| | Ann
|
|
|
|




.

Bob Phillips wrote:
|| - reduce complexity of formulas that have repeating functions -
|| create a name a make it more obvious and also shorter (e.g.
|| =IF(ISNA(VLOOKUP(A1,$H$1:$J$10,2,False)),
|| "",VLOOKUP(A1,$H$1:$J$10,2,False)) can be shortened to
|| =IF(ISNA(the_rate),"",the_rate) where the_rate is a name with a
|| Refersto value of =VLOOKUP(A1,$H$1:$J$10,2,False)

Doesn't that make formula auditing very difficult?


--
Interim Systems and Management Accounting
Gordon Burgess-Parker
Director
www.gbpcomputing.co.uk

Sorry, I don't understand what you mean.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gordon" wrote in message
...
Bob Phillips wrote:
|| - reduce complexity of formulas that have repeating functions -
|| create a name a make it more obvious and also shorter (e.g.
|| =IF(ISNA(VLOOKUP(A1,$H$1:$J$10,2,False)),
|| "",VLOOKUP(A1,$H$1:$J$10,2,False)) can be shortened to
|| =IF(ISNA(the_rate),"",the_rate) where the_rate is a name with a
|| Refersto value of =VLOOKUP(A1,$H$1:$J$10,2,False)

Doesn't that make formula auditing very difficult?


--
Interim Systems and Management Accounting
Gordon Burgess-Parker
Director
www.gbpcomputing.co.uk

You may want to look at Debra Dalgleish's technique for defining a name that can
grow/shrink when entries get added/removed.

http://www.contextures.com/xlNames01.html#Dynamic

And if you're working with names, do yourself a giant favor and get Jan Karel
Pieterse's (with Charles Williams and Matthew Henson) Name Manager

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

You may want to suggest it to users who think that they'll use names a lot (or
at all!).

Ann Shaw wrote:

Guys Guys Guys please - you both helped me a lot over here
in little olde Dublin, Ireland.

Much appreciated. Hope you got my email Bob - any help on
Macros would be great and thanks a million both Bob and
Jim!!!!

Kind regards

Ann
-----Original Message-----
Hi Jim,

Funny how language is so multi-facetted isn't it :-).
Makes being a
highly-paid lawyer easy :-(

Ann, we all wait with bated breath <g

Bob

"Jim Rech" wrote in message
. ..
When I read your response, Bob, I thought 'that's a
great tip but not what
the OP was asking'.<g So I thought I'd answer her
actual question which
I
took to be "how do I use the Create Names dialog?",
not "why should I use
names?".

If Ann comes back maybe she can tell us who was more
perceptive this
morning...<g

--
Jim Rech
Excel MVP
"Bob Phillips"
wrote in message
...
| Jim,
|
| That's a great tip, but (and this is not meant as
criticism, just extra
help
| for Ann) from the tone of the OP I think Ann would
benefit from some
ideas
| on how to deploy such names. Some occur to me, but as
I presume you
actually
| use this technique I will leave it to you to
|
| Regards
|
| Bob
|
|
| "Jim Rech" wrote in message
| ...
| Insert Names is a fast and convenient way to assign
defined names to
cells
| adjacent to labels, using those labels. An example
makes this easier
to
| follow:
|
| -Enter 3 names in cells A1:A3 (say Jones, Brown,
Smith).
| -Select the 6 cell range A1:B3.
| -Open the Insert Names dialog (Ctrl-Shift-F3 is a
shortcut).
| -Select the Left Column option (it may be selected
automatically).
| -Click OK.
|
| Cells B1:B3 will now have the names Jones, Brown
and Smith.
|
| Has you selected a range with more columns, like
A1:D3, then the names
| would
| have been assigned to the range B1:D1, B2:D2 and
B3:D3.
|
|
| --
| Jim Rech
| Excel MVP
| "Ann Shaw" wrote
in message
| ...
| | Hi
| |
| | I was recently asked in a training class why a
person
| | would use Insert Name Create and I did not know
about
| | this. I usually show my students how to define
names from
| | selected cells. Could you please give me an
example of
| | when I might use Create and what does the dialog
box mean
| | that pops up Top Row, Left Column etc..
| |
| | Many thanks
| |
| | Ann
|
|
|
|




.


--

Dave Peterson

Bob Phillips wrote:
|| Sorry, I don't understand what you mean.
||
|| --
||
|| HTH
||
|| RP
|| (remove nothere from the email address if mailing direct)
||
||
|| "Gordon" wrote in message
|| ...
||| Bob Phillips wrote:
||||| - reduce complexity of formulas that have repeating functions -
||||| create a name a make it more obvious and also shorter (e.g.
||||| =IF(ISNA(VLOOKUP(A1,$H$1:$J$10,2,False)),
||||| "",VLOOKUP(A1,$H$1:$J$10,2,False)) can be shortened to
||||| =IF(ISNA(the_rate),"",the_rate) where the_rate is a name with a
||||| Refersto value of =VLOOKUP(A1,$H$1:$J$10,2,False)
|||
||| Doesn't that make formula auditing very difficult?
|||

If you specify a NAME instead of "VLOOKUP(A1,$H$1:$J$10,2,False)" and you
need to find out why the formula (possibly) returns a wrong value, doesn't
it make that process more difficult?


--
Interim Systems and Management Accounting
Gordon Burgess-Parker
Director
www.gbpcomputing.co.uk

No, IMO it makes it easier as you already have the formula broken down into
components, and thus you can test those components separately, e.g.
=the_rate can be tested to see if that is where the problem lies. Component
development is a lot easier IMO and more productive, and although this is a
small example of such, I think it follows those principles.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gordon" wrote in message
...
Bob Phillips wrote:
|| Sorry, I don't understand what you mean.
||
|| --
||
|| HTH
||
|| RP
|| (remove nothere from the email address if mailing direct)
||
||
|| "Gordon" wrote in message
|| ...
||| Bob Phillips wrote:
||||| - reduce complexity of formulas that have repeating functions -
||||| create a name a make it more obvious and also shorter (e.g.
||||| =IF(ISNA(VLOOKUP(A1,$H$1:$J$10,2,False)),
||||| "",VLOOKUP(A1,$H$1:$J$10,2,False)) can be shortened to
||||| =IF(ISNA(the_rate),"",the_rate) where the_rate is a name with a
||||| Refersto value of =VLOOKUP(A1,$H$1:$J$10,2,False)
|||
||| Doesn't that make formula auditing very difficult?
|||

If you specify a NAME instead of "VLOOKUP(A1,$H$1:$J$10,2,False)" and you
need to find out why the formula (possibly) returns a wrong value, doesn't
it make that process more difficult?


--
Interim Systems and Management Accounting
Gordon Burgess-Parker
Director
www.gbpcomputing.co.uk

Ann

InsertNameCreateTop row/Left Column is a whole 'nother ball of wax than
InsertNameDefine.

Jim is on the right track as far as I'm concerned.

Then you can use intersect operations to return values from a grid.


Gord Dibben Excel MVP

On Thu, 3 Mar 2005 05:28:39 -0800, "Ann Shaw"
wrote:

Guys Guys Guys please - you both helped me a lot over here
in little olde Dublin, Ireland.

Much appreciated. Hope you got my email Bob - any help on
Macros would be great and thanks a million both Bob and
Jim!!!!

Kind regards

Ann
-----Original Message-----
Hi Jim,

Funny how language is so multi-facetted isn't it :-).
Makes being a
highly-paid lawyer easy :-(

Ann, we all wait with bated breath <g

Bob

"Jim Rech" wrote in message
.. .
When I read your response, Bob, I thought 'that's a
great tip but not what
the OP was asking'.<g So I thought I'd answer her
actual question which
I
took to be "how do I use the Create Names dialog?",
not "why should I use
names?".

If Ann comes back maybe she can tell us who was more
perceptive this
morning...<g

--
Jim Rech
Excel MVP
"Bob Phillips"
wrote in message
...
| Jim,
|
| That's a great tip, but (and this is not meant as
criticism, just extra
help
| for Ann) from the tone of the OP I think Ann would
benefit from some
ideas
| on how to deploy such names. Some occur to me, but as
I presume you
actually
| use this technique I will leave it to you to
|
| Regards
|
| Bob
|
|
| "Jim Rech" wrote in message
| ...
| Insert Names is a fast and convenient way to assign
defined names to
cells
| adjacent to labels, using those labels. An example
makes this easier
to
| follow:
|
| -Enter 3 names in cells A1:A3 (say Jones, Brown,
Smith).
| -Select the 6 cell range A1:B3.
| -Open the Insert Names dialog (Ctrl-Shift-F3 is a
shortcut).
| -Select the Left Column option (it may be selected
automatically).
| -Click OK.
|
| Cells B1:B3 will now have the names Jones, Brown
and Smith.
|
| Has you selected a range with more columns, like
A1:D3, then the names
| would
| have been assigned to the range B1:D1, B2:D2 and
B3:D3.
|
|
| --
| Jim Rech
| Excel MVP
| "Ann Shaw" wrote
in message
| ...
| | Hi
| |
| | I was recently asked in a training class why a
person
| | would use Insert Name Create and I did not know
about
| | this. I usually show my students how to define
names from
| | selected cells. Could you please give me an
example of
| | when I might use Create and what does the dialog
box mean
| | that pops up Top Row, Left Column etc..
| |
| | Many thanks
| |
| | Ann
|
|
|
|




.

As usual - you guys never let me down - I now fully
understand the use of Creating Names and it saves heaps
of time. Thanks for all your tips, websites and examples.

Indebted

Ann

-----Original Message-----
Ann

InsertNameCreateTop row/Left Column is a
whole 'nother ball of wax than
InsertNameDefine.

Jim is on the right track as far as I'm concerned.

Then you can use intersect operations to return values
from a grid.


Gord Dibben Excel MVP

On Thu, 3 Mar 2005 05:28:39 -0800, "Ann Shaw"
wrote:

Guys Guys Guys please - you both helped me a lot over
here
in little olde Dublin, Ireland.

Much appreciated. Hope you got my email Bob - any help
on
Macros would be great and thanks a million both Bob and
Jim!!!!

Kind regards

Ann
-----Original Message-----
Hi Jim,

Funny how language is so multi-facetted isn't it :-).
Makes being a
highly-paid lawyer easy :-(

Ann, we all wait with bated breath <g

Bob

"Jim Rech" wrote in message
. ..
When I read your response, Bob, I thought 'that's a
great tip but not what
the OP was asking'.<g So I thought I'd answer her
actual question which
I
took to be "how do I use the Create Names dialog?",
not "why should I use
names?".

If Ann comes back maybe she can tell us who was more
perceptive this
morning...<g

--
Jim Rech
Excel MVP
"Bob Phillips"
wrote in message
...
| Jim,
|
| That's a great tip, but (and this is not meant as
criticism, just extra
help
| for Ann) from the tone of the OP I think Ann would
benefit from some
ideas
| on how to deploy such names. Some occur to me, but
as
I presume you
actually
| use this technique I will leave it to you to
|
| Regards
|
| Bob
|
|
| "Jim Rech" wrote in message
| ...
| Insert Names is a fast and convenient way to
assign
defined names to
cells
| adjacent to labels, using those labels. An
example
makes this easier
to
| follow:
|
| -Enter 3 names in cells A1:A3 (say Jones, Brown,
Smith).
| -Select the 6 cell range A1:B3.
| -Open the Insert Names dialog (Ctrl-Shift-F3 is
a
shortcut).
| -Select the Left Column option (it may be
selected
automatically).
| -Click OK.
|
| Cells B1:B3 will now have the names Jones, Brown
and Smith.
|
| Has you selected a range with more columns, like
A1:D3, then the names
| would
| have been assigned to the range B1:D1, B2:D2 and
B3:D3.
|
|
| --
| Jim Rech
| Excel MVP
| "Ann Shaw"
wrote
in message
| ...
| | Hi
| |
| | I was recently asked in a training class why a
person
| | would use Insert Name Create and I did not
know
about
| | this. I usually show my students how to
define
names from
| | selected cells. Could you please give me an
example of
| | when I might use Create and what does the
dialog
box mean
| | that pops up Top Row, Left Column etc..
| |
| | Many thanks
| |
| | Ann
|
|
|
|




.


.