MATCH() irritating feature?
 

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

MATCH() irritating feature?
 

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

MATCH() irritating feature?
 

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.

"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

MATCH() irritating feature?
 

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