Naming cells #2

I want to give the same cell in two different worksheets the same name, 
"Company," but Excel won't allow me to. Is there a way to do that?
0
7/12/2005 3:46:02 PM
excel.misc 78881 articles. 5 followers. Follow

9 Replies
306 Views

Similar Articles

[PageSpeed] 42

Sheet1!Company and Sheet2!Company

-- 

HTH

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


"Watercolor artist" <Watercolorartist@discussions.microsoft.com> wrote in
message news:3DB83250-3F90-442D-BB71-00F78814641F@microsoft.com...
> I want to give the same cell in two different worksheets the same name,
> "Company," but Excel won't allow me to. Is there a way to do that?


0
bob.phillips1 (6510)
7/12/2005 4:17:38 PM
Watercolor artist wrote:
> I want to give the same cell in two different worksheets the same name, 
> "Company," but Excel won't allow me to. Is there a way to do that?

--------------

If by "worksheets" you actually mean worksheets and not workbooks, then I don't 
believe you can do what you want.  Range names are defined across the entire 
workbook, not for individual sheets.  That's kind of the point of names -- to 
make it easy to reference stuff from other sheets.

You might be able to convince "labels" to do what you want.  I've never played 
with them enough to know, but I believe they're local to a sheet rather than global.

Bill
0
wylie836 (251)
7/12/2005 4:20:58 PM
Not correct Bill.

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

-- 

HTH

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


"Bill Martin -- (Remove NOSPAM from address)" <wylie@earthNOSPAMlink.net>
wrote in message news:OUtlx2vhFHA.3448@TK2MSFTNGP12.phx.gbl...
> Watercolor artist wrote:
> > I want to give the same cell in two different worksheets the same name,
> > "Company," but Excel won't allow me to. Is there a way to do that?
>
> --------------
>
> If by "worksheets" you actually mean worksheets and not workbooks, then I
don't
> believe you can do what you want.  Range names are defined across the
entire
> workbook, not for individual sheets.  That's kind of the point of names -- 
to
> make it easy to reference stuff from other sheets.
>
> You might be able to convince "labels" to do what you want.  I've never
played
> with them enough to know, but I believe they're local to a sheet rather
than global.
>
> Bill


0
bob.phillips1 (6510)
7/12/2005 5:00:20 PM
Bob,

One of my sheets is called "June 05." When I change a cell to 'June 
05'!Company per your directions and press ENTER, the function becomes 
'MOD-job-report_2005.xls'!Company, which is the whole file's name. What am I 
doing wrong?

Howard

"Bob Phillips" wrote:

> Sheet1!Company and Sheet2!Company
> 
> -- 
> 
> HTH
> 
> RP
> (remove nothere from the email address if mailing direct)
> 
> 
> "Watercolor artist" <Watercolorartist@discussions.microsoft.com> wrote in
> message news:3DB83250-3F90-442D-BB71-00F78814641F@microsoft.com...
> > I want to give the same cell in two different worksheets the same name,
> > "Company," but Excel won't allow me to. Is there a way to do that?
> 
> 
> 
0
7/12/2005 5:38:11 PM
Bob Phillips wrote:
> Not correct Bill.
> 
> See http://www.xldynamic.com/source/xld.Names.html
> 

-----------

I'm a bit confused (a common state).  Is this global/local span something which 
has changed with Excel versions?  In my Excel97 I don't seem to have cell names 
local to a sheet as your web page describes.  The following steps illustrate:

1) define a cell to be named "Test" and verify I can access the cell using that 
name from that sheet.

2) go to another sheet and define a cell to be named "Test" and verify I can 
access the cell using that name.

3) go back to the first sheet, and the name "Test" now links to the cell on the 
second sheet -- even when used in the first sheet.

4) go into insert>name>define and I see that Excel only shows one cell by that 
name and it's linked to the second sheet (most recently created link).

Am I somehow creating the name incorrectly for local use, or does my Excel97 
behave differently from your version?  Or have I totally misunderstood?

Thanks...

Bill
0
wylie836 (251)
7/12/2005 6:06:17 PM
Bill Martin -- (Remove NOSPAM from address) wrote:
> Bob Phillips wrote:
> 
>> Not correct Bill.
>>
>> See http://www.xldynamic.com/source/xld.Names.html
>>
> 
> -----------
> 
> I'm a bit confused (a common state).  Is this global/local span 
> something which has changed with Excel versions?  In my Excel97 I don't 
> seem to have cell names local to a sheet as your web page describes.  
> The following steps illustrate:
> 
> 1) define a cell to be named "Test" and verify I can access the cell 
> using that name from that sheet.
> 
> 2) go to another sheet and define a cell to be named "Test" and verify I 
> can access the cell using that name.
> 
> 3) go back to the first sheet, and the name "Test" now links to the cell 
> on the second sheet -- even when used in the first sheet.
> 
> 4) go into insert>name>define and I see that Excel only shows one cell 
> by that name and it's linked to the second sheet (most recently created 
> link).
> 
> Am I somehow creating the name incorrectly for local use, or does my 
> Excel97 behave differently from your version?  Or have I totally 
> misunderstood?
> 
> Thanks...
> 
> Bill

---------

Never mind...  I understand now that the trick is to create the two cell names 
differently (by including the sheet name) and that Excel will then treat the two 
names as the same by dropping the sheet name when used locally.  Whew!

Thanks...

Bill
0
wylie836 (251)
7/12/2005 6:33:15 PM
Did you use Insert|Name|Define to create that sheet level name first?

If you work with names, do yourself a favor and get a copy of 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

Watercolor artist wrote:
> 
> Bob,
> 
> One of my sheets is called "June 05." When I change a cell to 'June
> 05'!Company per your directions and press ENTER, the function becomes
> 'MOD-job-report_2005.xls'!Company, which is the whole file's name. What am I
> doing wrong?
> 
> Howard
> 
> "Bob Phillips" wrote:
> 
> > Sheet1!Company and Sheet2!Company
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Watercolor artist" <Watercolorartist@discussions.microsoft.com> wrote in
> > message news:3DB83250-3F90-442D-BB71-00F78814641F@microsoft.com...
> > > I want to give the same cell in two different worksheets the same name,
> > > "Company," but Excel won't allow me to. Is there a way to do that?
> >
> >
> >

-- 

Dave Peterson
0
petersod (12004)
7/12/2005 6:39:55 PM
When you do Insert|name|Define, you get a dialog where you can enter the name of
the range and what it refers to.

If you put the sheet name in that "names in workbook" box, like:

Sheet1!Test
or
'Sheet 22 of 23'!Test

Then you've created a sheet level name.

Did you do it this way?

And xl97 to xl2003 have worked the same way.

I put this in the other branch of the thread, too...

If you work with names, do yourself a favor and get a copy of 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

(It's much easier to use that then the built in Insert|Names dialog.)

"Bill Martin -- (Remove NOSPAM from address)" wrote:
> 
> Bob Phillips wrote:
> > Not correct Bill.
> >
> > See http://www.xldynamic.com/source/xld.Names.html
> >
> 
> -----------
> 
> I'm a bit confused (a common state).  Is this global/local span something which
> has changed with Excel versions?  In my Excel97 I don't seem to have cell names
> local to a sheet as your web page describes.  The following steps illustrate:
> 
> 1) define a cell to be named "Test" and verify I can access the cell using that
> name from that sheet.
> 
> 2) go to another sheet and define a cell to be named "Test" and verify I can
> access the cell using that name.
> 
> 3) go back to the first sheet, and the name "Test" now links to the cell on the
> second sheet -- even when used in the first sheet.
> 
> 4) go into insert>name>define and I see that Excel only shows one cell by that
> name and it's linked to the second sheet (most recently created link).
> 
> Am I somehow creating the name incorrectly for local use, or does my Excel97
> behave differently from your version?  Or have I totally misunderstood?
> 
> Thanks...
> 
> Bill

-- 

Dave Peterson
0
petersod (12004)
7/12/2005 6:44:37 PM
"Bill Martin -- (Remove NOSPAM from address)" <wylie@earthNOSPAMlink.net>
wrote in message news:unM6sAxhFHA.708@TK2MSFTNGP09.phx.gbl...
> Bill Martin -- (Remove NOSPAM from address) wrote:
>
> Never mind...  I understand now that the trick is to create the two cell
names
> differently (by including the sheet name) and that Excel will then treat
the two
> names as the same by dropping the sheet name when used locally.  Whew!
>
> Thanks...
>
> Bill

Bill,

You beat me to the answer.

The other thing to be aware of is to have the sheet that the name applies
to, not necessarily the range that it refersto, to be active when you try to
create the local name, otherwise it will fail.


0
bob.phillips1 (6510)
7/13/2005 12:19:35 PM
Reply:

Similar Artilces:

Chart Names
Hi everyone, I'm sorry if this is an easy one but I just can't figure it out. I have a worksheet with 3 charts on it. I need to write some VBA to select each chart in turn and change its axis settings. So I figured the first thing to do is to name the charts so that I can select them properly in the VBA. But how do I name a chart??? If I select the chart I get "Chart Area" in the name box and I can't edit it. Many thanks for any help, Tony M As answered in .programming -- Hold the Ctrl key and click on the chart to select it. Click in the Name Box, type a new ...

macro to copy Vlookup formula to some cells with a filter on
Hello, I was wondering if somebody could help me with this macro… I have some data with a filter on, so that only the rows with blank cells are showing. I want to enter a VLOOKUP in the cell, and copy it down to all the blank cells. I tried just recording a macro, but when I run the macro it doesn’t work. Any ideas of how to do it? Example: 1) Before the filter: Colums A -B - C A x x - 1st Row A - 2nd Row A x x - 3rd Row A - 4th row A x x - 5th row 2) After the fil...

Naming a range
I have a such macro : Private Sub Macro1(arkusz As Worksheet) arkusz.Range(arkusz.PageSetup.PrintArea).Copy Sheets("Sheet1").Select Range("A" & Trim(Str(zLastRow))).Select Arkusz50.Paste Application.CutCopyMode = False ActiveWorkbook.Names.Add Name:="A1", RefersToR1C1:=Selection zLastRow = zLastRow + arkusz.Range(arkusz.PageSetup.PrintArea).Rows.Count Range("A" & Trim(Str(zLastRow))).Select ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell End Sub However the above macro doesnt work (this part: ActiveWorkbook.Names.Add Name:=&quo...

Envelope Journaling in Exchange 5.5 #2
Anyone know how to turn on envelope journaling in Exchange 5.5 (that is so journaling tracks the bcc info)? Journaling in Exchange 5.5 is addressed in MS KB 239427 (and in several articles for x2k and x2k3) but can't find anything for 5.5 envelope journaling Hi Ivan, It looks like this is a limitation of Journaling and how it works with messages. Basically, when a message has BCC recipients, the message is copied internally, the original instance has the TO and CC lists, the copy has the BCC lists. The original instance is being journalled, the BCC copy is not. Even with complete ...

Name Assignment
I need to assign a staff memeber to a group of clients Staff Names Clients Milk Shoe A-F Form Code G-L John DOe M-P The table that contains the client information CREATE TABLE [dbo].[FD__CLIENTS]( [OP__DOCID] [int] NOT NULL, [Fullname] [varchar](68) NULL, [NameF] [varchar](20) NULL, [NameL] [varchar](20) NULL, [NameMI] [varchar](20) NULL CONSTRAINT [PK_FD__CLIENTS] PRIMARY KEY CLUSTERED There is a staff table but there is nothing that ties it to the clients table. So the staf names will have to be hardcoded. Any ideas? On...

How to disable a cell . . . ??
How would I disable a cell so that it can't be used at all. I have created a form for users, and I want the cells that they can enter to be fixed. -- hays4 ------------------------------------------------------------------------ hays4's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28069 View this thread: http://www.excelforum.com/showthread.php?threadid=475813 You can lock the cells that the users shouldn't change (formulas/titles/descriptions) and unlock the cells that the user can change. Select the cell(s) format|cell|protection tab|check or un...

object not available #2
Okay, i found out that some system files were missing, and have replaced those. But now the macro crashes about halfway, again saying that it is missing an object. Most of the time the "references" option under the tools menu is greyed out, as is the add-in manager. As i am obviously a beginner, i don't know what is supposed to be referenced, or where to look for these references. Is there any way to get the macro to tell me what it is looking for? -- S-J ------------------------------------------------------------------------ S-J's Profile: http://www.excelforum.com...

If match copy cells in visual basic.
How do I write the visual basic code for the following. If the value in the cells in column A (sheet 1) matches the value in column A (sheet 2), copy contents of cells N, O & P, columns in the same row, to sheet 2 in A,B & C. Many thanks, in anticipation. Hi, I didn't quite follow your logic A (sheet 2), copy contents of cells N, O & P, columns in the same row, to > sheet 2 in A,B & C. This would overwrite the value in column A which I assume you don't want to do so instead this writes to columns B,C & D in sheet 2. This should work as worksheet code or...

How do I remove the sheet name from a named formula?
I would like to use the same name to refer to the same set of cells on different worksheets: SheetTitle=$A$1 but when I try this it reverts to SheetTitle=Sheet1!$A$1 Any ideas how to do it? I could use =IF(,,,) if I knew how to look up the currently active sheet; =IF(SheetName="Sheet1",TRUE,FALSE) Any ideas how to reference the name of the sheet? Well, you could do something like this: SheetTitle=!$A$1 The exclamation point means that the Name will refer to A1 on the active worksheet. Excel uses the SheetName to tell things apart. If I try to give to different A1'...

Strange Error (CRM 1.2)
Hi, I have a Virtual PC configuration set up for CRM demos. I have one image that has CRM 1.2 under Windows 2003 server. It is all configured and setup and running fine. I have another image with Windows XP configured. The two images see each other and communicate. From my Windows XP image I can access CRM both via browser and Outlook Client. However, occassionally I can't access the system via Outlook and when I try it via the browser I get a screen coming up with "Sonic Wall" on it asking for a login and password. If I enter in "Administrator" for the login...

Finding contents in a cell formula
Using XL 2003 & 97 Formula Cells are selected: Selection.SpecialCells(xlCellTypeFormulas, 23).Select ............ ............ As each cell is evaluated I need to know: If the cell contains a plus sign (+) AND with the following (very next position) being any digit (1234567890) -OR- If the cell contains a minus sign (-) AND with the following (very next position) being any digit (1234567890) -OR- If the very first position in the cell formula is a di...

Auto populate Email when Name is chosen
I have a list of names which i present in a combo box on the userform. I have an email textbox. I want the email to be auto-populated based on the name chosed. May I ask your help with this? Hi Jerry, Only the first procedure is really relevant in this post the others are to show how you may want to call it but, that entirely depends on your needs. Private sub BuildAndSend has 4 required parameters “ToWhom”, “Subject”,”Body”, and “Send”. Pass the appropriate values to the procedure and it will send or display an email to the user to send. HTH. Private Sub BuildAndSend( _ ...

If statement referring to a blank cell
I want to put an if statement in code that will say something like If a1 is blank then blah blah blah end if I can not seem to figure out how to refer to "blank" papa Sub blank() If Range(A1").Value = "" Then MsgBox "blah, blah, blah" Else: MsgBox "halb, halb, halb" End If End Sub Gord Dibben Excel MVP On 16 Mar 2005 15:28:11 -0800, "papa jonah" <adullam04-excelgoogle@yahoo.com> wrote: >I want to put an if statement in code that will say something like > >If a1 is blank then > >blah blah blah > &...

Startup #2
Is it possible to have Outlook startup Mail in the folder last viewed rather than always in the InBox? You haven't stated which version you are running. Please do not assume that all versions are always the same or that we have amazing ESP skills You can select a folder for each time it starts as the default, but it stays on that setting until you change it. Tools | Otions | Other | Advanced Options | startup in this Folder. I hope this helps you at least a little bit! Judy Gleeson MVP Outlook Outlook trainer and author of Productiv_IT with Outlook read my articles here: www.j...

Contact View #2
I did not see this so I'm trying to send it again. I am using Outlook 2000. I am trying to create a new card view and have added the Notes field but it doesn't show up. It shows up in the Detailed Address Cards view but I can't get it work in my own view. Anyone else had this problem and figured out how to get it to work? Thanks, Debbie ...

cell protection #3
We had a spreadsheet designed by a personn who took this spreadsheet to many different companies. He had some cells protected. He no longer works for the same company, no body else knows his worksheet and we don 't know the password to change some of these cells. Can anyone help? Is there anything that can be done? See http://www.mcgimpsey.com/excel/removepwords.html -- For everyone's benefit keep the discussion in the newsgroup. Regards, Peo Sjoblom "Deb" <rypac-deb@shaw.ca> wrote in message news:292F207E-EAC8-4101-B28A-3F0391C6A13E@microsoft.com... > W...

Custom entity with same display name as out of the box?
I tried to create a new entity "new_InvoiceDDI" with a display name of "Invoice" and it won't allow the display name to be the same as a "out of the box" entity. Why is this? Anyone know a way around it? CRM 4.0 Mark Hi Mark, There's no way around it except renaming the existing invoice entity to something else. One obvious reason is the Outlook Client. It uses the display names to show folders, but folders needs to have a unique name. The web client uses the display names as well, so how can a user differentiate between two entities having the...

Populate combobox with folder names
I had found this code to show folder names that displays in a msgbox, is it possible to get the list of folder names into a combobox? --------------------------------------- Sub ListFolders() Dim fs, f, f1, fc, s Dim folderspec folderspec = "C:\Excel\" Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.GetFolder(folderspec) Set fc = f.SubFolders For Each f1 In fc s = s & f1.Name s = s & vbCrLf Next MsgBox s End Sub ---------------------------------------- This is for exc...

Move cell values
Hi, Need a help in Macros. Suppose i have the foll. data ColA Col B 1 w 2 re 3 dff --> Empty cells 1 ed 2 23 3 24 I need to copy the cell with 3 in colA two rows up, so it should be someting like this: ColA Col B 1 w 3 dff 2 re 3 dff --> Empty cells 1 ed 3 24 2 23 3 24 Thanks for help. Hi ........., I think you messed up your...

Data Validation with a list that varies in size #2
I have created a template spreadsheet for a non-technical user On one tab, I have a comparison between Golfer A and Golfer B. On the front tab, there is a list of all the golfers for a particular weekend. I left 24 slots open. However, there is no requirement that all 24 are used. How do I eliminate blank rows from my dropdown list if that dropdown is filled with a Named Range and that range is defined as a column of 24 (potential) name cells. For example, if I have a Range of 24 cells A2:A25 (A1 = "Name") defined as GolferList, but only A2:A11 are filled, I want my dropdown list ...

Combine data from 2 queries
I have 2 tables of data that have sku #, size and color. Table 1 is a reference with all items. Table 2 has on hand data. I am looking to create a query to pull into table 2 additional information from table 1. I have attempted to create a query for each table and add in a concatenation of sku, size and color so that I can join the tables and extract information to create a table and/or report. When I attempt to create 1 query from both of these queries I'm getting error messages...help - is there an easier way to build this (I will need to refresh my tables on a reg...

Multiple Domains #2
Hello everyone, I have an Exchange 2000 standard server, and I have a small number of new users that need a different extension on their email accounts. For example we are .com.au and they need to be .co.jp can I do this in a single domain or do I need to create a whole new Exchange server (or move to enterprise exchange) to do this. The funny thing is that it is only 5 or 6 users so I want to avoid having to upgrade my Exchange version if I can get away with it. Thanks Tim Wallace Hi Tim, Just ensure you have created MX records for the additional domains, and have created recipient ...

Instance name must be the same name as the computer name ...
I've seen many posts about this installation issue and ran across it myself during an install on an existing SBS server. When running Select @@servername I received a null answer. I looked in the sysservers table in the master database and found that there was no '0' server listed as I've seen on all other installs of SQL. I took a chance and changed the value to 0, restarted SQL and ran the @@servername and it now came back as the name of the server as expected and I was able to install CRM with no problems. Long term I don't know if this will cause issues with ...

Why data in cell doesn't stay aligned with cell borders when prin.
The problem appears in "portrait" but not landscape. The misalignment gets worse toward the bottom of the page. ...

Change the name of an employee class
Is there a way in the SQL table to change the name of employee classes? Patti, Where did you want to change them (cards, trx, history, etc). There are several tables involved. Would it be possible to make new classes starting with the class you want to duplicate as a default. Please let us know what you are trying to accomplish by changing classes. Kind regards, Leslie "Patti Anderson" wrote: > Is there a way in the SQL table to change the name of employee classes? Our naming convention has changed, and requires changing the names of all employee classes...