• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

hide formula but display the result without protecting worksheet in excel

Netsy

New Member
hi,

in excel sheets v.2016, i have several cells with formula.
How can i hide formula but display the result without protecting worksheet in excel. plz help.
 

Peter Bartholomew

Well-Known Member
If you use Name Manager define a named formula, then all that will be seen in the formula bar is something of the sort
= total
which describes the result but not its calculation. Rather more advanced would be to then use the immediate window of the VBA editor to hide the name.
Code:
thisworkbook.Names("total").Visible=False
so that it no longer appears in the list of names.
 

NARAYANK991

Excel Ninja
If you use Name Manager define a named formula, then all that will be seen in the formula bar is something of the sort
= total
which describes the result but not its calculation.
Hi ,

But couldn't the Evaluate Formula feature be used to get to the formula behind the named range ?

Narayan
 

Peter Bartholomew

Well-Known Member
@NARAYANK991 True, 'Step In' within Evaluate Formula will expose the formulas if it is not combined with sheet protection.
It does place the formula a further step out of reach but all that is needed is to follow the steps.

It is interesting that Microsoft, in an article on performance, recommend not nesting named formulas yet I have always found it to be effective. That includes formulas that go 6 deep and even inverting a different 30x30 matrix for each row of the output table. Not that any of that is going to help the OP.
 
Top