Sheet won't reference a named cell

[Mike H]

I'm getting a bizarre error with a named cell.
The first tab has three cells named SN WO & DC.
Three other tabs reference those cells directly: "=SN".
But a fourth tab can't see it. If I type "=SN" into a cell
Excel will popup a file browser expecting me
to find a file with that name.
If I copy a working "=SN" from another tab,
I get an error saying that name is already on this tab.
This particular tab was copied from another spreadsheet,
but this does not appear to be linked back to that spreadsheet.
Does anyone know what's going on here?

Mike Henry

 

[Pete_UK]

Select the rogue sheet and do Insert | Name | Define to see if there
are any names peculiar to that sheet, which will show the sheet name
in front of the name of the cell (I'm sure you know this already,
Mike).

Hope this helps.

Pete

 

[Mike H]

That's a good idea, but it only gets more bizarre.
I'm working on a copy of the live spreadsheet and the names
on the rogue tab are pointing back to the live sheet, but on the
good tabs they are pointing to the the local tab 1.
And the Define box won't let me redirect to the local sheet.
It lets me select a new target but then reverts back after I close Define.
There is no password or protection on that sheet, so I
don't know what's going on. For now, I can copy the
sheet to a blank sheet (but not CopySheet), but then
I'll lose the page formats and footer.

---------------------------

 

[Pete_UK]

I've found that if you copy a sheet from one file to another (or drag
it), then the named ranges in the original file come with the sheet,
and thus point back to that file. Can you delete the name SN from the
rogue sheet? If so, then anything that uses the name SN will then
revert to the one defined elsewhere in the (local) file.

Hope this helps.

Pete

 

[Mike H]

Charts do the same thing and it drives me crazy. But the SN link was not on
the page when I copied it from the previous file, it was added months later,
so somehow the whole page must be linked back to the original file. Plus,
when I tried to delete the external SN, it deleted both it and the internal
SN.