Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
range name global
hi community
how can i create a range name that can be use within 2 worksheets, example in worksheet 1, i have range from A1:A3 and name that as "Fruits" also in worksheet 2, i have range from A1:A3 with similar data and also wanted to name that as "Fruits" my objective is as and when I am in worksheet 1 or worksheet 2, all i need to do is click the Name Box and select "Fruits", excel will select for me the range (A1:A3) if I am in worksheet 1 and when at worksheet 2, excel will select A1:A3 in worksheet 2 thanks community for the assistance :) -- oldLearner57 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
range name global
Select A1:A3 on worksheet1 (let's assume the sheet name is "Worksheet 1"),
click Insert/Name/Define and incorporate the worksheet name in the name of your range. For example: name = 'Worksheet 1'!Fruits repeat the process for sheet2. This way, you can declare several local named ranges that share the same name. Bob Phillips has some additional info he http://xldynamic.com/source/xld.Names.html "oldLearner57" wrote: hi community how can i create a range name that can be use within 2 worksheets, example in worksheet 1, i have range from A1:A3 and name that as "Fruits" also in worksheet 2, i have range from A1:A3 with similar data and also wanted to name that as "Fruits" my objective is as and when I am in worksheet 1 or worksheet 2, all i need to do is click the Name Box and select "Fruits", excel will select for me the range (A1:A3) if I am in worksheet 1 and when at worksheet 2, excel will select A1:A3 in worksheet 2 thanks community for the assistance :) -- oldLearner57 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
range name global
thanks "JMB" for the guidance...
perhaps my request was not properly stated... what I am looking for is.. assumed I had data already entered into range of cells say at worksheet 1 - range a1:a3 (with data) worksheet 2 - range a1:a3 (with data - similar with worksheet 1) how do i declare a name range for both of them without having to manually selecting worksheet 1 and perform the Name Range steps thereafter go into worksheet 2 and repeat the similar steps as for worksheet 1 it is ok for just say 2 or 3 worksheets but if say, I have 25 worksheets and wanted to have their similar name range (with similar data) all to have a "Name" and whenever I am in whichever worksheet, all I need to do is go to the Name Box and click the Name. hope now this is clearer or that is no possible way to have it done in this way ??? many thanks for the guidance :) thanks to community as well :) -- oldLearner57 "JMB" wrote: Select A1:A3 on worksheet1 (let's assume the sheet name is "Worksheet 1"), click Insert/Name/Define and incorporate the worksheet name in the name of your range. For example: name = 'Worksheet 1'!Fruits repeat the process for sheet2. This way, you can declare several local named ranges that share the same name. Bob Phillips has some additional info he http://xldynamic.com/source/xld.Names.html "oldLearner57" wrote: hi community how can i create a range name that can be use within 2 worksheets, example in worksheet 1, i have range from A1:A3 and name that as "Fruits" also in worksheet 2, i have range from A1:A3 with similar data and also wanted to name that as "Fruits" my objective is as and when I am in worksheet 1 or worksheet 2, all i need to do is click the Name Box and select "Fruits", excel will select for me the range (A1:A3) if I am in worksheet 1 and when at worksheet 2, excel will select A1:A3 in worksheet 2 thanks community for the assistance :) -- oldLearner57 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
range name global
On sheet1 select A1:A3
InsertNameDefineMyName =!$A$1:$A$3 Note: won't show up in the NameBox but can be entered in F5 dialog box. Gord Dibben MS Excel MVP On Thu, 22 Nov 2007 00:50:01 -0800, oldLearner57 wrote: thanks "JMB" for the guidance... perhaps my request was not properly stated... what I am looking for is.. assumed I had data already entered into range of cells say at worksheet 1 - range a1:a3 (with data) worksheet 2 - range a1:a3 (with data - similar with worksheet 1) how do i declare a name range for both of them without having to manually selecting worksheet 1 and perform the Name Range steps thereafter go into worksheet 2 and repeat the similar steps as for worksheet 1 it is ok for just say 2 or 3 worksheets but if say, I have 25 worksheets and wanted to have their similar name range (with similar data) all to have a "Name" and whenever I am in whichever worksheet, all I need to do is go to the Name Box and click the Name. hope now this is clearer or that is no possible way to have it done in this way ??? many thanks for the guidance :) thanks to community as well :) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
range name global
On second thought, this can give you a problem.
The name if used in a formula like =SUM(MyName) is not volatile so won't update without an F2ENTER Best to download Jan Pieterse's Name Manager Add-in. http://www.oaltd.co.uk/MVP/Default.htm Gord On Thu, 22 Nov 2007 08:13:00 -0800, Gord Dibben <gorddibbATshawDOTca wrote: On sheet1 select A1:A3 InsertNameDefineMyName =!$A$1:$A$3 Note: won't show up in the NameBox but can be entered in F5 dialog box. Gord Dibben MS Excel MVP On Thu, 22 Nov 2007 00:50:01 -0800, oldLearner57 wrote: thanks "JMB" for the guidance... perhaps my request was not properly stated... what I am looking for is.. assumed I had data already entered into range of cells say at worksheet 1 - range a1:a3 (with data) worksheet 2 - range a1:a3 (with data - similar with worksheet 1) how do i declare a name range for both of them without having to manually selecting worksheet 1 and perform the Name Range steps thereafter go into worksheet 2 and repeat the similar steps as for worksheet 1 it is ok for just say 2 or 3 worksheets but if say, I have 25 worksheets and wanted to have their similar name range (with similar data) all to have a "Name" and whenever I am in whichever worksheet, all I need to do is go to the Name Box and click the Name. hope now this is clearer or that is no possible way to have it done in this way ??? many thanks for the guidance :) thanks to community as well :) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
range name global
thanks Gord Dibben for the info and guidance :)
much appreciated thanks community as well :) -- oldLearner57 "Gord Dibben" wrote: On second thought, this can give you a problem. The name if used in a formula like =SUM(MyName) is not volatile so won't update without an F2ENTER Best to download Jan Pieterse's Name Manager Add-in. http://www.oaltd.co.uk/MVP/Default.htm Gord On Thu, 22 Nov 2007 08:13:00 -0800, Gord Dibben <gorddibbATshawDOTca wrote: On sheet1 select A1:A3 InsertNameDefineMyName =!$A$1:$A$3 Note: won't show up in the NameBox but can be entered in F5 dialog box. Gord Dibben MS Excel MVP On Thu, 22 Nov 2007 00:50:01 -0800, oldLearner57 wrote: thanks "JMB" for the guidance... perhaps my request was not properly stated... what I am looking for is.. assumed I had data already entered into range of cells say at worksheet 1 - range a1:a3 (with data) worksheet 2 - range a1:a3 (with data - similar with worksheet 1) how do i declare a name range for both of them without having to manually selecting worksheet 1 and perform the Name Range steps thereafter go into worksheet 2 and repeat the similar steps as for worksheet 1 it is ok for just say 2 or 3 worksheets but if say, I have 25 worksheets and wanted to have their similar name range (with similar data) all to have a "Name" and whenever I am in whichever worksheet, all I need to do is go to the Name Box and click the Name. hope now this is clearer or that is no possible way to have it done in this way ??? many thanks for the guidance :) thanks to community as well :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Global Save As? | Excel Discussion (Misc queries) | |||
=OFFSET(!$A$1,,,,) - global range naming | Excel Worksheet Functions | |||
Range name: Change from sheet specific to global | Excel Discussion (Misc queries) | |||
Global Calculation ??? | Excel Discussion (Misc queries) | |||
Global Function | Excel Discussion (Misc queries) |