OK, I think I have solved it without using global names.
Originally, the name Rates!Year referred to
=OFFSET(Rates!$A$3,0,1,1,COUNTA(Rates!$3:$3)-1)
So all I needed to do was delete the name entirely and use the above formula
to the right of the "=" sign wherever I was previously referring to it by
name. It means that the formula is recalculated several times on each
occasion of a workbook recalculation, but the incidence is not so great as
to be noticeable.

"Dave Peterson" wrote in message
...
Do you have to use a local name for Year? Can you make it global?

(I'd also stay away from a name that was the same as a built in worksheet
function name =year().)

I used Year1 as a global name (workbook level) and things stayed the way
you
want.

If you have to use a local name (worksheet level), I think I'd make it a
multiple step process.

#1. Copy the sheet (keeping all the page setup/filters/etc)
#2. Rename the sheet
#3. Copy the cells (ctrl-a a few times will select them all) from the
original
sheet and paste them over the new sheet.

But it is an interesting "feature". I've never noticed it before.

Jack Sheet wrote:

Hi all

I have a worksheet called "2006" within which cell E6 contains the
formula:
=MATCH(E7,Rates!Year,0)
The value of E7 is the worksheet's name derived from
=--RIGHT(CELL("filename",E7),LEN(CELL("filename",E7))-FIND("]",CELL("filename",E7)))

When I duplicate that worksheet within a workbook
(edit/copy or move sheet/copy and move to end)
then the cell E6 in the new worksheet contains the formula
=#N/A
I would prefer that it EVALUATES to #N/A but the underlying formula would
remain
=MATCH(E7,Rates!Year,0)
Then when I correct the worksheet name the error result would disappear.

I don't suppose there is any way around this?

Thanks

--

Dave Peterson