Go Premium for a chance to win a PS4. Enter to Win


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

Posted on 2011-02-18
Medium Priority
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!
Question by:Daniele Questiaux
  • 3
  • 3
LVL 50
ID: 34930830

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

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

Author Comment

by:Daniele Questiaux
ID: 34931284

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!
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 50

Accepted Solution

Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 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

Author Comment

by:Daniele Questiaux
ID: 34932756
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.!

Author Closing Comment

by:Daniele Questiaux
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".

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

885 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