Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 . |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 | | | | |
#7
|
|||
|
|||
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 | | | | |
#8
|
|||
|
|||
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 | | | | . |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
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 |
#13
|
|||
|
|||
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 |
#14
|
|||
|
|||
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 | | | | . |
#15
|
|||
|
|||
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 | | | | . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sorting names alphabetically when names may start with numbers | Excel Discussion (Misc queries) | |||
Creating "factorial" result from three lists... | Excel Discussion (Misc queries) | |||
Pull unique names for drop down list | Excel Discussion (Misc queries) | |||
creating names for data shown on a scatter plot | Charts and Charting in Excel | |||
How can I find the common names in two columns of names? | Excel Discussion (Misc queries) |