How to view a hidden/protected formula in Excel

[MeMe]

Hi there,

someone in the past has created a formula in a protected field. Recently the
formula comes up with a negative number, so we know something is wrong, but
we can't see the formula in order to review it and find the problem. You
can't even go to the Name Box to show its content.

Without being able to unprotect the cells that contain the formula, is there
a way to view the formula?

thanks,

 

[CyberTaz]

Not that I've been able to figure out Of course, I'm far from being the
absolute authority. As far as I can determine, though, unless
Tools>Protection>UnProtect Sheet doesn't prompt you for a password you don't
have you're pretty much helpless.

Check back periodically for other thoughts.

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[JE McGimpsey]

Hi there,

someone in the past has created a formula in a protected field. Recently the
formula comes up with a negative number, so we know something is wrong, but
we can't see the formula in order to review it and find the problem. You
can't even go to the Name Box to show its content.

Without being able to unprotect the cells that contain the formula, is there
a way to view the formula?

If the workbook is *yours* or *your company's*, you can use the
techniques here to remove sheet protection

http://www.mcgimpsey.com/excel/removepwords.html

If they are NOT yours, go back to the developer/owner and ask that
he/she modify it.

 

[MeMe]

Hi JE,

people sometimes hoard information so they seem more important. As sad as
this is, it is a company file that 10 people update daily. Now I can at least
figure out why we are getting a negative number.

Much thanks,

M

 

[Carl Witthoft]

Do a search on the net for "password.xla" At least thru Office 2003
that (as an add-in) will bypass worksheet passwords just ducky.

In an emergency, email me, and I might be able to track down a copy

 

[JE McGimpsey]

Do a search on the net for "password.xla" At least thru Office 2003
that (as an add-in) will bypass worksheet passwords just ducky.

In an emergency, email me, and I might be able to track down a copy

Or you could just follow the link I posted, which has a link to
password.xla...<g>

FWIW, it's no better or worse than the .xls file on my site - the
"protection" in XL is so primitive that it's trivial to bypass.

 

[MeMe]

Hi JE,

thanks again for helping. I have another question if you don't mind.

Since the file is all fixed and such, I was wondering if there was a way to
protect a cell's contents from being edited, but to allow a formula to be
shown?

thanks,

M

 

[CyberTaz]

Use Protect Sheet as usual but first
HTH |:>)
Bob Jones
[MVP] Office:Mac
clear the checkbox for Hidden in the Format> Cells - Protection dialog for
any cells whose content you want to display in the Formula Bar.