Define Local Name using Name Box

I have an Excel 97 file example with sheets "Red"
and "Blue".  In cell Red:A1 the range name "SheetName" 
has been defined.  I would like to define the same range
name "SheetName" in cell Blue:B1 as a local name, using
the Name Box on the left-hand side of the formula bar, 
rather than Insert | Name | Define from the menu.
 
I have seen the syntax previously on this, and have
forgotten the procedure.
 
Thanks for your help.
Chris S.

0
yeahright (8)
9/26/2003 5:53:06 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
296 Views

Similar Articles

[PageSpeed] 29

Just include the sheet name in the name box:

    Sheet2!SheetName

note that SheetName will still be a workbook-level name unless you 
redefine it.

In article <05fa01c38457$09f6db60$a301280a@phx.gbl>,
 "Chris S" <yeahright@nospam.com> wrote:

> I have an Excel 97 file example with sheets "Red"
> and "Blue".  In cell Red:A1 the range name "SheetName" 
> has been defined.  I would like to define the same range
> name "SheetName" in cell Blue:B1 as a local name, using
> the Name Box on the left-hand side of the formula bar, 
> rather than Insert | Name | Define from the menu.
>  
> I have seen the syntax previously on this, and have
> forgotten the procedure.
>  
> Thanks for your help.
> Chris S.
>
0
jemcgimpsey (6723)
9/26/2003 5:58:59 PM
This does not seem to work if the name has already been 
defined on another worksheet.  When I type 

Blue!SheetName

Excel takes me to the "Red" sheet and the original name 
definition.

More help please.

Chris S.


>-----Original Message-----
>Just include the sheet name in the name box:
>
>    Sheet2!SheetName
>
>note that SheetName will still be a workbook-level name 
unless you 
>redefine it.
>
>In article <05fa01c38457$09f6db60$a301280a@phx.gbl>,
> "Chris S" <yeahright@nospam.com> wrote:
>
>> I have an Excel 97 file example with sheets "Red"
>> and "Blue".  In cell Red:A1 the range name "SheetName" 
>> has been defined.  I would like to define the same range
>> name "SheetName" in cell Blue:B1 as a local name, using
>> the Name Box on the left-hand side of the formula bar, 
>> rather than Insert | Name | Define from the menu.
>>  
>> I have seen the syntax previously on this, and have
>> forgotten the procedure.
>>  
>> Thanks for your help.
>> Chris S.
>>
>.
>
0
nospam7515 (2086)
9/26/2003 7:30:55 PM
My mistake - you can use that if you're in Insert/Name/Define, but 
not with the name box if a workbook-level name is defined.

Use Insert/Name/Define to delete the workbook level name, then use 
the namebox for the sheetx!sheetname.

In article <0c4a01c38464$b4af4fd0$a101280a@phx.gbl>,
 "Chris S" <nospam@nospam.com> wrote:

> This does not seem to work if the name has already been 
> defined on another worksheet.  When I type 
> 
> Blue!SheetName
> 
> Excel takes me to the "Red" sheet and the original name 
> definition.
> 
> More help please.
0
jemcgimpsey (6723)
9/26/2003 7:40:46 PM
Reply:

Similar Artilces:

Using worksheet name as reference
HI I have one master sheet with around 50 rows in it. For each Row there is corrosponding worksheet. e.g. Row #23 will have corrosponding worksheet named '23'. I want to populate some data from worksheet 23 in Row no. 23 in master sheet. How should I refer the sheet 23 automatically using Data 23 in cell in the row ? If type ='23'!B21 I can get it but I want to automate it so I'm refering to cell instead of 23 which is ='A12'!B21 here it gives error because I'm making formating error. Pl. help . thanks. Hi, Try this: =INDIRECT("'"&ROW()...

Collections Management
Dynamics GP v9 - Collections Management Users are unable to use "user defined letters" (Word Documents) for mass mailings. They are limited to only using predefined letters, or printing each letter individually for every customer. Error received when users attempt to use a custom letter: -------------------------------------------------- Unhandled script exception: Cannot find report "COL_Reminder_UpcomingDue". EXCEPTION_CLASS_SCRIPT_MISSING SCRIPT_CMD_REPORT -------------------------------------------------- ---------------- This post is a suggestion for Microso...

Using Money 2005 on a second machine
I normally use Money 2005 on my desktop. I have it in a shared folder. I travel quite a lot and need to be able to use Money while I'm away. I installed Money 2005 on my laptop (I checked this would be OK before buying Money) and I synchronise the data file from my desktop to my laptop at least weekly. I only use Money on my laptop or my desktop - never both at the same time. Whenever I try to open my Money data file from my laptop, I get a message that the file needs to be updated and then a second message shortly afterwards telling me that Money can't open the file. The sof...

Cannot read Notes contacts by using Outlook
Hi, all, I have the following installed: MS Outlook 2003 MS Outlook Connector for Domino (Outlook 2003/2002 Add-in: Notes Connector: http://www.microsoft.com/downloads/details.aspx?FamilyID=8ebbba59-5f17-4e52-8980-c4f0dfa92d65&DisplayLang=en) Domino Notes Client 5.0.11 With Outlook Connector, I can smoothly sync between my Outlook and Notes, except that my Outlook cannot sync the Personal Address Book of my Notes. My Outlook can view all the server address book of Notes but cannot view the local Personal Address Book of Notes. What should I do? Many thanks. ...

Find a name in a colum and insert a row
I have a large worksheet with over 1000 rows of information. I would like to develop a VBA macro to open a search text box and position the cursor to the cell that matches the text input. ( similar to the index option in help) When enough characters have been entered I would like to add a row below the existing row and position the cursor to the first cell in the row. Any code or help would be appreciated! :) ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ You may...

Requery combo box in subform
I am having a problem with requerying combo box controls on my subform. In the after update event for the subform I have the following code to requery one of the combo boxes: Me![icudx2].Requery What is happening is that the selection made in the combo boxes for record 1 is being cleared when I select the combo box for record two. In Access 2003 I have a subform that is linked to the main form with a linked field called studyid. The source for the main form is tblsection3 and the source for the subform is tblDiagnosis. There is a one to many relationship between the tables with re...

return the staff # to the right of the names
I have a workbook with 2 sheets, one sheet named “sheet1”, another named “sheet2”. In sheet 1, I have 2 columns. Column A is the names, column B is the Staff #.s A B 1 Name Staff Number 2 Tom 235 3 Jack 345 4 Linda 888 … In sheet 2, I have the names in column A, but the column B is empty: A B 1 Name Staff Number 2 Jack 3 Linda 4 Joe … In sheet 2, column B, I want the computer to return me the correspond staff numbers which are listed in sheet 1: I want 345 in B2, 888 in B3. See my page on VLOOKUP http://www.mvps.org/dmcritchie/excel/vlookup.htm and please sign your name at t...

Localized Date
I am looking for a strategy to localize the date as we recieve if from the database. All dates are stored on the server based on the database server's local time. Most of the SQL statements are inline and ad-hoc using ado and vb6. What I am hoping to avoid is adding date manipulation logic in those inline sql statements. Since some of the recordsets are data bound to repeaters or grids, I cannot imagine looping over the recordset updating the date - besides the sheer waste of processor time on the client side. It looks like my best option atm would be to create a custo...

Chart title = Worksheet Name?
I have a workbook where every sheet represents a different day. The title of the sheet is the date e.g. 9-14-06. On each sheet is a chart whose title is same date as on the tab, but formatted slightly differently: Thursday <cr> September 14, 2006. Is there a way to easily link the two so when I update the tab date, the chart date changes also? Thanks, Bill Halper I do hope someone can find a shorter way but here is mine. Firstly, the name on the tab will not be recognized as text by Excel, so wee need to get the sheet name into a cell and then extract year, month day A1 (sheet na...

proper name conversion
I have a database that has names of people in one field, in a last name, first name order. (example: Smith, John) How can i seperate the last and first names from one text field into two different fields? Or at least have only the last name. thanks for any help. Howard wrote: >I have a database that has names of people in one field, >in a last name, first name order. (example: Smith, John) >How can i seperate the last and first names from one text >field into two different fields? Or at least have only >the last name. thanks for any help. > > This is air ...

Application-defined or object-defined error
Hi, I find myself stumped by an incredibly easy piece of code and one that I have used before. I am getting the error: Run-time error '1004': Application-defined or object-defined error I am getting the error when I run the following code: Private Sub Workbook_Open() With Application .ScreenUpdating = False .DisplayAlerts = False Workbooks.Open "\\depot02\rel\www\internal\business_areas\edg\Metrics\Phones\HighHoldTimesDetailed.xls" Workbooks("HighHoldTimesDetailed.xls").Worksheets("data").Cells.Copy _ Workbooks("phoneholdtime...

Copying To Excel Using Macro
I can get the subject, attachment info and date from an email to be copied to excel, but does anyone know a way to get lines from the actual message. Is there some property of the Folder.Items that gets me this? My problem is that I have a huge set of emails whose messages look something like this: Name: John Smith Telephone: 555-555-5555 and I need to write a macro to automatically copy these to a database. Any advice will be appreciated. Thank you. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet me...

Auto-select combo box entry when only one row
I have 2 combo boxes the second is dependent on the first. When the first is clicked, I requery the second in the OnClick event. When the requeried combo box has only one row, I'd like this to be auto selected. How can I do this? Thanks. "mscertified" <rupert@tigerlily.com> wrote in message news:3AACB546-54A0-4BC2-8AFA-03AEB37F001E@microsoft.com... >I have 2 combo boxes the second is dependent on the first. When the first >is > clicked, I requery the second in the OnClick event. When the requeried > combo > box has only one row, I'd like this to be...

Using Multidimensional Array
I am using multidimensional array in my function. dim aReturnValue() iIndexValue = 0 If iOccurrence > 0 Then bStringFound = True ' Prepare the return value ReDim Preserve aReturnValue(iIndexValue, 3) aReturnValue(iIndexValue,0) = sMatchedValue aReturnValue(iIndexValue,1) = iLineCount+1 aReturnValue(iIndexValue,2) = iOccurrence aReturnValue(iIndexValue,3) = sListOfMatchFoundItems iIndexValue = iIndexValue + 1 End If Problem is I am getting subscript out of range in ReDim Preserve aReturnValue(iIndexValue, 3) I checked it on the net... I came to know that When usi...

retrieval of deleted mail boxes
Is it possible to retrieve a deleted mail box? One that would have been deleted about a month ago? (No longer on backup tape) Thanks Hi Valerie, If the mailbox has been deleted, and if the deleted mailbox retention period has elapsed, and you have no tape backup from which to restore, I'm afraid the mailbox and its data are gone permanently. What version of Exchange are you running, and do you know the user whose mailbox you are trying to recover ever used an Outlook client offline? If so, there might be a client-side OST (offline store) that might contain some or all of the...

How to use the same drop down fields
How do I use the same drop down fields in multiple forms without recreating the values with each form? On Sun, 15 Nov 2009 08:51:01 -0800, Petra van Vuurem <Petra van Vuurem@discussions.microsoft.com> wrote: >How do I use the same drop down fields in multiple forms without recreating >the values with each form? The only way to do that is to prepare a macro that populates the list. Here's some sample code (see http://www.gmayor.com/installing_macro.htm if needed): Sub PopulateCityDropdown() Dim FieldName As String Dim WasProtected As Boolean ...

Duplicate local folders displayed with duplicate entries in calander
Hi, I have two "Local Folders" displayed in my "All Mail Folders" and I can't delete one of them. This poses a problem for me with the program Agendus and my calander entries. Aside from that I want to get rid of one of them as they are duplicates. I have no idea how to do it. I might add that this is a new computer and I used the Microsoft transfer program to bring all my settings over, so this might be one of the issues. If anyone has a solution please try to be specific so that I can walk myself through it. Thanks very much in advance Bob Reynolds It'...

Mutual Fund Names and Stock Names displayed on multiple lines
In 2005 Deluxe, Portfolio Manager, Group by Investment Type, the same mutual fund is incorrectly listed on multiple lines. And, if I move the cursor over different lines, the names sometime change. The mkt values appear correct, but the fund names are all messed up. Is there an update or service pack ready yet to fix this and/or other problems? -- Greg Have the same problem with money 2005 canadian version the Symbol,Name,Last Price and %Change change to the line the cursor was on before all else stay the same. Rudolf "Greg" wrote: > In 2005 Deluxe, Portfolio Manage...

runtime error 2465: application-defined or object-defined error
I'm trying to use a button on a main form to change the sort order of a sub- subform.When I execute the following, I get runtime error 2465: application- defined or object-defined error. opting to debug, I find the line with orderbyon highlighted. Anybody see what's wrong? Private sub timesort_click() Forms![control]![worklist]![approved].Form.OrderBy = "Forms![control]! [worklist]![approved]![time]" Forms![control]![worklist]![approved].Form.OrderBy0n = True End Sub -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/a...

Can i setup outlook local(.pst) on citrix diff from my LAN?
I work on different network than my company LAN.Today we access outlook through web access.But this doesn't give me option for local .PST saving. Is it possible to have local mailbox in this situation? If so what needs to be done? Thank You Rambabu Try asking the guys that support the Exchange server if they support RPC over HTTPS (aka Outlook Anywhere). If they say yes, then ask them if they would help you setup Outlook 2003/2007 on your machine. "Rambabu" <Rambabu@discussions.microsoft.com> wrote in message news:15C16257-E787-4B11-A238-C8E61590DD8...

VBA Conditional Formating Using Logical Expression
I am new to VBA so please provide as much explanation in your responses as possible as I would love to learn more. I have a set of data (call it range 1) that resides in specific cells (all in one column) and does not change. I have another set of data (call it range 2) that is spread across 5 columns and occupies the same rows as range 1. Range 2 values in each of the 5 columns relate to the Range 1 data in the same row. I need to set up more than three conditions using >, <, = or n/a to determine the color of each of the cells in range 2. As data is entered into th...

Naming a formfield
I am using Word 2003, I have added a formfield with code as shown below, declared the ffield as a formfield but the code is debugging with object variable not set for some reason on the '.Name'. I have successfully used this bit of code in other templates I have created. Can anyone offer me an alternative or solution please? Set ffield = _ ActiveDocument.FormFields.Add(Range:=Selection.Range, _ Type:=wdFieldFormTextInput) With ffield .Name = "contaminant" With .Text...

Defining a variable to = MATCH
Dim thingy As Integer thingy = "=MATCH(""DiceC"",qperiodagentperformance!A:A,0)" ActiveCell.Formula = _ "=INDEX(qperiodagentperformance!D" & thingy & ":D13000,MATCH(""Agen Summary"",qperiodagentperformance!A" & thingy & ":A13000,0))" Range("D26").Select my thingy doesnt seem to work..... no comment Any suggestions on correcting this? Mik -- Message posted from http://www.ExcelForum.com Try with thingy = Application.Match("DiceC", Sheets("qperiodagentperformance"...

using IIF to determine form state
hey all, I have a query that displays the content of a cd. on the main form i work with, the media form can be displayed as a subform of either Forms!frmMain!subfrm or Forms!frmMain!subfrmflt In the criteria for one of the fields, it references a value on the media form ([Text60]) this is how i'm trying to set the criteria: "[Forms]![frmMain]![" &iif(forms!frmmain!subfrmflt.visible=true,"subfrmflt","subfrm") & "]![Text60]" basically throwing an iif in the middle to determine whether the floating form is visible. I know it will work if ...

how to do self-defined regression in excel?
Hi, I want to do regression ananlysis in excel using a sigmoid function, which is not offered in the general 6 functions in excel(by adding trendline). How can I do it? Thanks. The Analysis ToolPak - VBA addin has a regression function and probably everything else you want. Tools > AddIns then checkmark Analysis ToolPak - VBA Go back to Tools dropdown and it should be listed near the bottotm of the dropdown. >-----Original Message----- >Hi, I want to do regression ananlysis in excel using a >sigmoid function, which is not offered in the general 6 >functions in excel(by a...