Creating names

  • Thread starter Thread starter Ann Shaw
  • Start date Start date
A

Ann Shaw

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)
 
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
| 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
 
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
| 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
|
|
| | > 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
| > | > | 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
 
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
 
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?
 
Sorry, I don't understand what you mean.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
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!).
 
Bob Phillips wrote:
|| Sorry, I don't understand what you mean.
||
|| --
||
|| HTH
||
|| RP
|| (remove nothere from the email address if mailing direct)
||
||
|| ||| 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?
 
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)
 
Ann

Insert>Name>Create>Top row/Left Column is a whole 'nother ball of wax than
Insert>Name>Define.

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
 
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
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA - retrieve data on last create named range from previous inserted named range 0
Add a new row in Named Table (Table1) and add data from txtboxes into last empty cells based on header named of the Table1 0
Creating names for individual cells over a range 1
Counting Down to a Blank Row 6
Excel Is this possible?? 3
how do i split names in to two columns 2
Linked cells between worksheets and sorting 2
Template formatting disappears 1

Back
Top