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