Solved

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

Posted on 2011-02-18
6
450 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

863 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

25 Experts available now in Live!

Get 1:1 Help Now