Naming Excel cells on multiple worksheets

  Nosmas 22:11 27 Sep 2008
Locked

My system is XP SP2 (and fully up to date with MS Updates) and MS Office 2000 which of course includes Excel 2000.

I have a workbook for calculating personal Income Tax using a separate worksheet for each tax year. When a new tax year starts in April, I copy the previous year's sheet, rename it and amend various data items such as Allowances and Tax Rates. I have named many of the data cells for use in various formulae, so these names appear on all worksheets, and (unless I make any changes to the layout), the same cells on each worksheet have the same name but possibly a different value.

I now wish to add a new cell name (e.g. L11 to be named SavLmt) in three of the worksheets but cannot get the name to display instead of the cell reference in the name box. I followed the Help instructions under topic 'Name cells on more than one worksheet by using a 3-D reference'. I have tried clicking on Add, OK and Close in the dialog box but without success. If I click on Insert > Name > Define > SavLmt, the 'Refers to' box correctly identifies the names of the first and last worksheets followed by the cell reference $L$11.

Can someone (VoG?) tell me what I am doing wrong or need to do so that the name for L11 appears in the name box on each of the three worksheets?

  VoG II 22:43 27 Sep 2008

I think that this is an issue of what is displayed in the address bar rather than your named ranges not being set up correctly. If you create a named range on a single sheet then when you click in that cell the name (rather than the cell address) is displayed in the address bar. It seems that this is not the case when the name refers to the same cell on different worksheets.

Just to test this I created a named range 'aname' referring to B1 on Sheet1:Sheet3. On another sheet I entered the formula =SUM(aname) and the correct result was returned.

  VoG II 23:34 27 Sep 2008

P.S. This seems to be a useful link click here

  Nosmas 15:36 28 Sep 2008

VoG™ Thank you for your replies and the link. I am beginning to think that the '3-D Reference' method is not the one to use, but at the moment I cannot find another way of doing what I want.

When I click on Insert > Name > Define the dialog box displays all the cell names for that worksheet down the left and on the right it shows the just the name of the Sheet Tab. Clicking on any of these then shows in the 'Refers to' box the Sheet Tab followed by the address of the cell. I rather think this layout is the result of copying an entire worksheet into a new sheet at the beginning of the tax year. The contents of some of these cells change from one year to the next, and formulae in that sheet correctly use the value in the named cell ON THAT SHEET.

My experiments with trying to give the same name to the same cell address on EACH worksheet have resulted in the definition being located in one sheet and the value in that cell being used by formulae in other sheets no matter what value is held in the 'active' sheet, which is of course the way the '3-D Reference' method is intended to work.

Does anyone know of a way to 'manually' do what the sheet copying instruction seems to do automatically?

  VoG II 23:32 28 Sep 2008

I'm sorry but I don't really understand the problem. Perhaps you should post to a specialist Excel forum click here

  Nosmas 01:22 29 Sep 2008

VoG™ Thanks for your further post and I will have a look at the link you gave.

Sorry if I have not made clear my problem, so I will try again.

Let us call the Sheet Tabs Sheet X, Sheet Y and Sheet Z. If one clicks on Insert > Name > Paste the names of ALL cells in the workBOOK are listed alphabetically down the left hand side without any cell addresses on the right hand side, (although these will be given if one clicks on 'Paste List' and pastes them into an unused part of the sheet). If however one chooses Insert > Name > Define then the list displays ALL the named cells in the workBOOK AND opposite each cell name that is used IN THAT workSHEET is the cell address ON THAT SHEET. The right hand side is blank for any cell names NOT used in that workSHEET.

Having 'played around' trying to solve my problem, the present position with cell L11 selected on each sheet, the name box shows on Sheet X the address L11, Sheet Y SavLmt and Sheet Z address L11. Any calculations on sheets X or Z using SavLmt will take the value shown in L11 on Sheet Y even though the L11 values are different on Sheets X and Z. If I now go to Sheet X or Z, display the Define dialog box and scroll down and select SavLmt the 'Refers to' box shows ='Sheet Y'!$L$11 If I now delete the contents of 'Refers to' and substitute ='Sheet X:Sheet Z'!$L$11 now the name box on all three sheets shows L11 and the cell name SavLmt no longer shows. Furthermore any cell formula using SavLmt on any of the three sheets is displaying #VALUE! i.e. no value can be found for SavLmt despite the fact that in the Define dialog SavLmt in the 'Refers to' box is shown as ='Sheet X:Sheet Z'!$L$11

What I want is for the name SavLmt to appear in the name box on ALL THREE sheets and for formulae using SavLmt on ANY sheet to use the value in L11 OF THAT SHEET. As I said in my first post, at the start of the new tax year I copy the previous year's sheet IN ITS ENTIRETY to a new sheet, and Excel automatically 'tags' the cell names displayed in the Define dialog with the name of the new sheet. Hence my question is there a way to do his manually?

  Nosmas 15:23 01 Oct 2008

Glad to say my problem has been resolved thanks to the link given by VoG. I registered with that site and in response to my post I received a reply from a MVP.

For the benefit of others who might hit the same problem, its all down to understanding the difference between Global and Local names. Just entering the desired name into the Names in Workbook box of the Define Name dialog and the cell address (preceded by a '=' sign) for that name in the 'Refers to' box defines a Global name. This can be accessed from any other sheet in the workBOOK or even from another workBOOK by using the correct syntax.

However to create a Local name it is necessary to prefix the name keyed into the Names in workbook box with the name of the workSHEET. Upon clicking the Add button the sheet name (followed by '!') is placed between the '=' sign and the cell address. When the named cell is made 'active' its name is displayed in the Name Box on that sheet and the value in that cell can be used in formulae. If an identically named cell in another sheet is defined (as Local) then it can have a different value to the same names in other work sheets.

  VoG II 15:28 01 Oct 2008

Thanks for the feedback. I don't use named ranges much so I failed to really grasp the problem.

Pedro (pgc01) is pretty good, isn't he :o)

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Best phone camera 2017

Stunning new film posters by Hattie Stewart, Joe Cruz & more

iPad Pro 10.5in (2017) review

28 astuces pour profiter au mieux de votre iPhone