Solved

copy named ranges from one worksheet to the next worksheet in excel

Posted on 2011-02-18
6
452 Views
Last Modified: 2012-05-11
I am lost with named ranges.
I am setting up formulas on a template sheet with named ranges etc. I want to copy the sheet cells with the formulas on the other 17 sheets I have but the named ranges remain linked to that first sheet. I cannot find how to ensure that the named range becomes localised everytime I work on a sheet, and only refers to that sheet. I have searched the database here, and looked up google, and even downloaded Name Manager, but when I ask it to globalise the name, nothings happens. I am really totally baffled by ho to get about for a problem that seemed to me to be trivial at start!
I am sure it is simple, but I do not even start to see how to go about! I changed the entry of the name and removed the sheet and "!", but when I close, it reverts back to the old name! When I use name manager, and want to globalise, it tells me it is globalised, so???
I want the same cells in everysheet with the same name but referring to the cells in each sheet when that sheet is active.
Thank you!
0
Comment
Question by:daniques
  • 3
  • 3
6 Comments
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 34930830
Hello,

when you define the ranges in the first sheet, you need to set the scope to the sheet instead of the workbook. You cannot change the scope of a range name after it has been defined.

 scope  scope cannot be changed
With all the range names referring to the sheet, you can then copy the sheet and the names in the new sheet will refer to that sheet.

See attached. Copy sheet1, which has 2 names and see that there will now be four names, two referring to Sheet1 and the other two referring to the copy of Sheet1.

cheers, teylyn

NameScope.xlsx
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 34930845
Mind you that you cannot simply copy the cells to a new sheet. You need to copy the whole sheet in order to carry over the range names that refer to that new sheet.
0
 

Author Comment

by:daniques
ID: 34931284
Hi!

I did not mention that I am still using excel 2003 where there is no entry for the scope. I suspect it is a very good addition to excel 2007 and 2010?
Sorry not to have been clear at the start!
Danièle
0
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst earned 500 total points
ID: 34931299
No problem. You can define scope in 2003 and earlier, but it is not as apparent.

If you want to name a cell with a sheet scope, then you have to enter the name in the name box preceded by the sheet name. See the screenshot. I'm in the process of defining the range name "redcell" with a scope of Sheet1. The name "greencell" has already been defined and the scope shows in the list of range names.

 range name scope in 2003
Again, you can not change the scope after the name has been defined. If you get it wrong, delete the name and start over.

cheers, teylyn
0
 

Author Comment

by:daniques
ID: 34932756
Thanks!
I sent a comment a few hours ago to thanks you for the really thorugh answer but it does not seem to have gone through? Sorry about that because your reply was so quick, and I tried it straight away. What was the essential problem turned out to be the fact that I was copying PART of the sheet containing the named range, but not the WHOLE sheet as you mentioned in your short extra comment.Thank you for pointing out that single important and essential point.
As expected, all is now working wonderfully well.!
Danièle
0
 

Author Closing Comment

by:daniques
ID: 34932762
The extra comment about copying the sheets as a WHOLE rather than only part of it was the crucial point, and made the solution "foolproof".
Thanks!
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question