named cells. need a 101

I've been building a 'what if' sheet that pulls in this and that from
other sheets.  All the sheets have named cells, as does the what if'
sheet itself.  I want to copy/replicate this sheet multiple times in
the wb to allow for multiple and concurrent what if setups.  I am
worried about named resources (cells) and what's going to happen when
I attempt this.  I will experiment, but I'm worried about something
getting picked up in the wrong place.    Is there a good 101 out there
that discusses the use of names and what to expect?

Thank you.
0
cate
1/5/2010 11:15:29 PM
excel.programming 6508 articles. 2 followers. Follow

1 Replies
957 Views

Similar Articles

[PageSpeed] 24

There was an earlier posting regarding using the same name within a workbook 
on different sheets.  Essentially, it concluded that you would need to 
modify
the "Names in Workbook" name to differentiate between different sheets, 
since all names are at the workbook level and not the sheet level. 
Otherwise, when you use Range("somename") in code, it would only apply to 
the active sheet, which would involve some very meticulous code writing to 
avoid putting or retieving data from different locations.


"cate" <catebekensail@yahoo.com> wrote in message 
news:981624b0-788a-464b-9048-547a63a17190@e37g2000yqn.googlegroups.com...
> I've been building a 'what if' sheet that pulls in this and that from
> other sheets.  All the sheets have named cells, as does the what if'
> sheet itself.  I want to copy/replicate this sheet multiple times in
> the wb to allow for multiple and concurrent what if setups.  I am
> worried about named resources (cells) and what's going to happen when
> I attempt this.  I will experiment, but I'm worried about something
> getting picked up in the wrong place.    Is there a good 101 out there
> that discusses the use of names and what to expect?
>
> Thank you. 


0
JLGWhiz
1/6/2010 12:45:02 AM
Reply:

Similar Artilces:

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...

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 ...

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...

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...

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'...

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 > &...

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...

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...

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...

Need to copy from word 2007 and paste in another program
I am typing files in word and then copying them to another program. I could do it once, and now the copy and paste will not light up. I made sure nothing is in the clip board, as I had trouble with it copying things I did not want. I have cleared clipboard and still cannot copy. I have a new computer and it is frustrating when easy things are so difficult. Can someone please help me? I am taking an online class and need to submit this paper. Is there anyway to get rid of clipboard? Thank you for your help. -- Lyn K On Wed, 17 Mar 2010 17:45:01 -0700, LynK <LynK@discussi...

Axapta Developer NEEDED!
Hello: I'm a recruiter for a $600 million sales company. We are looking for an individual to work out of our beautiful corporate offices in Southern California. This individual would have one year experience doing Axapta development. I can't find anyone...please help! ...

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...

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 ...

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...

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. ...

Cell Compare
I have cell A:A and B:B, and i want C:C to list what is in A:A but no in B:B, and I want D:D to list what is in B:B but not in A:A, thank for any info -- wally020 ----------------------------------------------------------------------- wally0206's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1499 View this thread: http://www.excelforum.com/showthread.php?threadid=26614 Chip Pearson has information on working with lists. There's a formula here to extract values that are in one list, and not another: http://www.cpearson.com/excel/duplicat.htm#InOneN...

Need help with OutputTo an Excel Spreadsheet
I have a table (APPROVED FOR CAL OUTGOINGt1) that needs to be exported to pre-existing spreadsheet "IMPORT DATA" tab. The spreadsheet is located on the desktop. How do I do this? Look here: http://msdn.microsoft.com/en-us/library/bb214134.aspx Regards Kevin "DevilDog1978" <DevilDog1978@discussions.microsoft.com> wrote in message news:8C0B05FF-AFC9-4B30-9E84-4519AF501C20@microsoft.com... >I have a table (APPROVED FOR CAL OUTGOINGt1) that needs to be exported to > pre-existing spreadsheet "IMPORT DATA" tab. The spreadsheet is locate...

Named Reference
Hi, Can someone tell me what the pros and cons are (if any) of these 2 ways of naming a range. ActiveWorkbook.Names.Add NAME:="HOME", RefersTo:=ActiveCell Range("HOME").Select or Dim HOME As Range Set HOME = ActiveCell HOME.Select Thanks, DaveU It seems to me that the first method creates a named range that is essentially meaningless to the user and is only used for VBA processing. Additionally, if you don't write code to delete the named range it will just sit there in the workbook waiting for somebody to ask what it means. The second alternative is a clea...

"Name Conflict" when Excel 2007 open an old worksheet
Hi all, An "Name Conflict" error message pop-up "Name cannot be the same as a built-in name". That worksheet was created by Excel 2000 and also could open on Excel 2003 without any error message. Change the worksheet file name could not solve the problem after tested. Why? Thank you, Chik "Chik" <c...@netvigator.com> wrote... >An "Name Conflict" error message pop-up "Name cannot be the same as >a built-in name". That worksheet was created by Excel 2000 and also >could open on Excel 2003 without any error message. Change th...

Find a time value in one column based on names in another
Hi - I have a list of names. (40 Different names that may occur 30 - 40 times a piece in column B). In Column C I have a list of times that each person made an entry. Is there a formula that will give me the earliest time that appears in column C for each person on the list in column B? I already have a list of all of the unique names that will appear in column B that I can use as a reference. Example: col A col B col C smith 6:00am jones 5:03pm smith 7:05am jones 4:02pm adams 2:05pm adams 2:33p...

convert from last name, first name TO first name, last name
How can I convert my contacts (as a group menaing all of them at once0 from the last name, first name to first name last name, without having to enter EACH cotnact and perform it almost 400 times? Also, in what format would you suggest trying to print my contacts showing fields name mobile, home, work phone and email address? I assume there is a way to make used defined fields? You'll need to create a post with enough information that others could understand it. How are we to know which field or view you are trying to "convert?" Be specific. I would suggest pri...