Solved

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

Posted on 2011-02-18
6
449 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:teylyn
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:teylyn
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 50

Accepted Solution

by:
teylyn 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now