local v global names, and data validation by list

[Jack Sheet]

Hi.

I have a globally defined named range called "Year" that refers to
=Rates!$B$3:$X$3

In a worksheet "Model (1)" I have a B1 cell on which the data validation is set to "Pick from list", and that list is defined as
=Year

So far so good. I believe that this would not have worked had the name "Year" been localised to "Rates!Year". A named range referred to in data validation has to be either local to the worksheet containing the validated cell, or else a global name. It cannot be localised to another sheet.

Also in worksheet "Model (1)" I have another cell C1 which contains a formula
=Match($B$1,Year,0)

I wish to create a duplicate of worksheet "Model (1)", which will be called "Model (2)". In the worksheet "Model (2)" I wish to be able to select an entry for cell B1 by Pick from list, with reference to the list contained in the range Year. And I should like the corresponding cell C1 in "Model (2)" to evaluate to =Match('Model (2)'!B1,Year,0).

At some later date I may wish to add additonal entries to Year, without having to update all of the "Model (x)" worksheets.

As it stands, the whole thing falls down, because when I create a duplicate of "Model (1)", the cell 'Model (2)'!C1 evaluates to #N/A while the Pick from list validation in cell 'Model (2)'!B1 is disabled, because the named range referred to in the validation evaluates to an error.

The cause of the problem seems to be that when I duplicate Model (1), Excel also creates a duplicate of the name "Year" which it localises to "Model (2)", and both the validation and formulae choose to refer to the localised name in preference to the global one

I could solve this by VBA, so that whenever I create a duplicate worksheet I delete the corresponding localised name, and correct the formula and validation as necessary in cells B1 and C1 of the new worksheet, all within the VBA routine. But I would rather have a solution that does not involve VBA (there are currently no VBA modules in this workbook and I would rather it stayed that way).

This is surely a wheel that has already been invented, so I should be grateful for some pointers.

Thanks

 

[Andrew Poulsom]

I can't reproduce that behaviour in Excel 2000. If I copy sheet Model (1), the new sheet becomes Model (2) and all works fine. There is only one instance of the name Year.

Are you sure that the name Year doesn't appear locally on Model (1)?

 

[Jack Sheet]

Hi Andrew, and thank you for taking the trouble to look at my problem.

I am on Excel 2002 SP3, but this particular problem has cropped up before for me on various versions, and I have never resolved it.

I confirm that there is no name "Year" other than the global name at the point of duplication.

I have uploaded an illustration here:
http://www.keepandshare.com/doc/view.php?u=785204
This zip file contains two workbooks, one showing the position immediately before duplicating the worksheet and one showing the position after.
Perhaps this will clarify where I go wrong.

 

[Andrew Poulsom]

It's because your name Year refers to:

=OFFSET(Rates!YearCell,0,1,1,Rates!Years)

and the 2 names in the reference are local to sheet Rates. Make those names global and you won't have a problem. If you do that the reference will become:

=OFFSET(Before.xls!YearCell,0,1,1,Before.xls!Years)

which you should amend to:

=OFFSET(YearCell,0,1,1,Years)

It's a pity you didn't mention how Year was defined in your original post. Had you done so you would have had a solution much sooner.

 

[Jack Sheet]

It's a pity you didn't mention how Year was defined in your original post. Had you done so you would have had a solution much sooner.

Thanks again Andrew. Yes it is a fine art trying to distill what information is relevant to the problem and what is not.