Links in Object "Hidden Name"

[Carl]

I just found 4,700 bad references (#REF!) in my workbook. The software used
to find the "#REF!" link gave the message, "Found links in Hidden name
[worksheet name]!Uniformances17R147C5:='[worksheet name]'!#REF!" Does anyone
know how this might be happening? I know it means I have lost the reference
in a formula by doing something like deleting a worksheet it was referencing
but that has not happened. Thanks in advance.

 

[Jon Peltier]

Do you have an add-in or utility running that is creating these names? In
VBA you can hide defined names from the UI.

Jan Karel Pieterse's Name Manager (http://jkp-ads.com) is much more capable
at handling names than Excel's UI. It allows you to see all names, including
hidden names, and you can filter on a number of parameters. I'm sure this
would let you see where these names are and delete the names with errors.
Maybe even figure out where they are coming from.

- Jon

 

[Jon Peltier]

Why do you have hidden charts?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

Thanks, John. I only have MS Query installed. I am doing a lot of deleting
and adding charts (or was before). Is there a correct way to delete a
chart
without leaving hidden charts in the workbook? The Name Manager utility is
great. Thanks! I still need to find out what I am doing to create the
problem
however.

Do you have an add-in or utility running that is creating these names? In
VBA you can hide defined names from the UI.

Jan Karel Pieterse's Name Manager (http://jkp-ads.com) is much more
capable
at handling names than Excel's UI. It allows you to see all names,
including
hidden names, and you can filter on a number of parameters. I'm sure this
would let you see where these names are and delete the names with errors.
Maybe even figure out where they are coming from.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

I just found 4,700 bad references (#REF!) in my workbook. The software
used
to find the "#REF!" link gave the message, "Found links in Hidden name
[worksheet name]!Uniformances17R147C5:='[worksheet name]'!#REF!" Does
anyone
know how this might be happening? I know it means I have lost the
reference
in a formula by doing something like deleting a worksheet it was
referencing
but that has not happened. Thanks in advance.

 

[Jon Peltier]

How about hitting the Delete key?

If you're using Excel 2002 or 2003, you can add a button to the Drawing
toolbar that will help you select shapes. First go to the View menu, click
Toolbars, then select Drawing from the list. Click on the little Add or
Remove Buttons arrow at the end of the toolbar, click Drawing, then select
Select Multiple Objects from the bottom of the list. Click this button and
you will get a list of all objects on the sheet, so you can select any that
you didn't know were there. Then use the Delete key to remove them.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

I don't really know. I had hundreds of hidden buttons and charts, all at
the
top of the sheets in rows that were NOT hidden. I was able to use the
utility
to locate and delete them but don't want to repeat the proplem. Sometimes
I
delete charts or buttons by hitting "cut" and then just not pasting it
anywhere. Does this creat hidden objects by chance? Thanks again.

Why do you have hidden charts?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

Thanks, John. I only have MS Query installed. I am doing a lot of
deleting
and adding charts (or was before). Is there a correct way to delete a
chart
without leaving hidden charts in the workbook? The Name Manager utility
is
great. Thanks! I still need to find out what I am doing to create the
problem
however.

:

Do you have an add-in or utility running that is creating these names?
In
VBA you can hide defined names from the UI.

Jan Karel Pieterse's Name Manager (http://jkp-ads.com) is much more
capable
at handling names than Excel's UI. It allows you to see all names,
including
hidden names, and you can filter on a number of parameters. I'm sure
this
would let you see where these names are and delete the names with
errors.
Maybe even figure out where they are coming from.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


I just found 4,700 bad references (#REF!) in my workbook. The
software
used
to find the "#REF!" link gave the message, "Found links in Hidden
name
[worksheet name]!Uniformances17R147C5:='[worksheet name]'!#REF!"
Does
anyone
know how this might be happening? I know it means I have lost the
reference
in a formula by doing something like deleting a worksheet it was
referencing
but that has not happened. Thanks in advance.

 

[Carl]

I am using 2000. I guess it is time to upgrade. Thanks a lot for the great
suggestions.

How about hitting the Delete key?

If you're using Excel 2002 or 2003, you can add a button to the Drawing
toolbar that will help you select shapes. First go to the View menu, click
Toolbars, then select Drawing from the list. Click on the little Add or
Remove Buttons arrow at the end of the toolbar, click Drawing, then select
Select Multiple Objects from the bottom of the list. Click this button and
you will get a list of all objects on the sheet, so you can select any that
you didn't know were there. Then use the Delete key to remove them.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

I don't really know. I had hundreds of hidden buttons and charts, all at
the
top of the sheets in rows that were NOT hidden. I was able to use the
utility
to locate and delete them but don't want to repeat the proplem. Sometimes
I
delete charts or buttons by hitting "cut" and then just not pasting it
anywhere. Does this creat hidden objects by chance? Thanks again.

Why do you have hidden charts?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


Thanks, John. I only have MS Query installed. I am doing a lot of
deleting
and adding charts (or was before). Is there a correct way to delete a
chart
without leaving hidden charts in the workbook? The Name Manager utility
is
great. Thanks! I still need to find out what I am doing to create the
problem
however.

:

Do you have an add-in or utility running that is creating these names?
In
VBA you can hide defined names from the UI.

Jan Karel Pieterse's Name Manager (http://jkp-ads.com) is much more
capable
at handling names than Excel's UI. It allows you to see all names,
including
hidden names, and you can filter on a number of parameters. I'm sure
this
would let you see where these names are and delete the names with
errors.
Maybe even figure out where they are coming from.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


I just found 4,700 bad references (#REF!) in my workbook. The
software
used
to find the "#REF!" link gave the message, "Found links in Hidden
name
[worksheet name]!Uniformances17R147C5:='[worksheet name]'!#REF!"
Does
anyone
know how this might be happening? I know it means I have lost the
reference
in a formula by doing something like deleting a worksheet it was
referencing
but that has not happened. Thanks in advance.

 

[Jon Peltier]

2000's still pretty useful, some of my clients are still using it to good
effect. If you upgrade, go only to 2003 until you've investigated the pros
and cons of converting to Office 2007.

If you just want to get rid of ALL charts on the sheet, go to the VB Editor
(press Alt+F11 to switch quickly), open the Immediate Window (Ctrl+G), paste
in this line of code, and press Enter.

activesheet.chartobjects.delete

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

I am using 2000. I guess it is time to upgrade. Thanks a lot for the great
suggestions.

How about hitting the Delete key?

If you're using Excel 2002 or 2003, you can add a button to the Drawing
toolbar that will help you select shapes. First go to the View menu,
click
Toolbars, then select Drawing from the list. Click on the little Add or
Remove Buttons arrow at the end of the toolbar, click Drawing, then
select
Select Multiple Objects from the bottom of the list. Click this button
and
you will get a list of all objects on the sheet, so you can select any
that
you didn't know were there. Then use the Delete key to remove them.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

I don't really know. I had hundreds of hidden buttons and charts, all at
the
top of the sheets in rows that were NOT hidden. I was able to use the
utility
to locate and delete them but don't want to repeat the proplem.
Sometimes
I
delete charts or buttons by hitting "cut" and then just not pasting it
anywhere. Does this creat hidden objects by chance? Thanks again.

:

Why do you have hidden charts?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


Thanks, John. I only have MS Query installed. I am doing a lot of
deleting
and adding charts (or was before). Is there a correct way to delete
a
chart
without leaving hidden charts in the workbook? The Name Manager
utility
is
great. Thanks! I still need to find out what I am doing to create
the
problem
however.

:

Do you have an add-in or utility running that is creating these
names?
In
VBA you can hide defined names from the UI.

Jan Karel Pieterse's Name Manager (http://jkp-ads.com) is much more
capable
at handling names than Excel's UI. It allows you to see all names,
including
hidden names, and you can filter on a number of parameters. I'm
sure
this
would let you see where these names are and delete the names with
errors.
Maybe even figure out where they are coming from.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


I just found 4,700 bad references (#REF!) in my workbook. The
software
used
to find the "#REF!" link gave the message, "Found links in Hidden
name
[worksheet name]!Uniformances17R147C5:='[worksheet name]'!#REF!"
Does
anyone
know how this might be happening? I know it means I have lost the
reference
in a formula by doing something like deleting a worksheet it was
referencing
but that has not happened. Thanks in advance.