MATCH() irritating feature?

[Jack Sheet]

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]

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.

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

 

[Jack Sheet]

Thanks Dave

I have a morbid and doubtless irrational aversion to using global named
ranges.
On this particular occasion I intend that the user should be able to bolt
the workbook onto any other workbook that he
has, by selecting all of the worksheets and Copy/Move the whole block of
them into another workbook. Provided that there are no global ranges in
*my* workbook I can be confident that this will not cause problems, but if
there are global named ranges that duplicate global named ranges in the
destination workbook it could cause problems. I shall try to think of
another way around it.
Thanks.

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.

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

 

[Jack Sheet]

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.

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.

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