Hiding the sheet name of a formula in a cell

[DoctorV]

I have a very sensitive Excel workbook with Salary Rating Info. The
formulas do lookups and other calculations against hidden worksheets
within the workbook. I do not want people to see the worksheet
references at all in the formula bar of the cell they are in. How can
i keep these formulas working without letting users see what worksheet
the formula refers to? Thanks

 

[Max]

Select the target cells
(Hold down Control key to make
multiple discontiguous cell selections with the mouse)

Click Format > Cells > Protection tab > Check "Hidden" > OK

Then apply sheet protection via:

Click Tools > Protection > Protect Sheet > Enter password > Confirm > OK

The formulas will be hidden in the target cells

 

[DoctorV]

Thanks for the reply.

My boss did not wish to hide the entire formula or the formula bar.
Just the sheet name as in the example provided.

 

[Max]

ok, here's what you added on to your original post after posting (you
"edited" in Excelforum)
(Btw, it doesn't appear in the newsgroup thread)
-----------
I just want a way to clean up my formulas by deleting the sheet reference
not hide the formula bar!!!
Example:
In the case of this =Payment_Info!A1*0.15
Have the part that says Payment_Info!A1 not be visible or be replaced with
verbiage like Confidential

Last edited by DoctorV on 01-20-2004 at 03:41 PM
------

Try using named ranges?

Name the cell A1 in Payment_Info as : Confidential

Then instead of : =Payment_Info!A1*0.15

you can put: =Confidential*0.15

----------
But note that savvy users / readers could get at the underlying sheet
references via:

Insert >Name > Define or
Insert > Name > Paste > Paste List

unless you apply sheet protection (to all visible sheets)
and workbook protection as well

 

[DoctorV]

I just wanted to replace everything to the left of the ! with the word
Confidential

 

[Arvi Laanemets]

Hi

You can't hide a part of formula - but you can hide whole formulas (as Max
explained).
You also can use cascaded link instead of direct one. An example:

Your source data are on sheet 'Payment Info'. There you have a lot of
various information, some of it is linked to input sheet, some not. And you
wnat to hide the last part of info!
Create a sheet p.e. PI, and link to this sheet only those cells from
'Payment Info', you want to use in formulas on input sheet, like
=IF('Payment Info'!A1="","",'Payment Info'!A1)
In all formulas on input sheet, replace sheet name 'Payment Info' with 'PI'
Activate VBA editor (Alt+F11), In VBA-Project window select sheet PI, and
set it's Visible property to xlSheetVeryHidden (when you don't see
properties window, you have to activate it from View menu)
Right-click onto your VBA-Project and select from drop-down menu VBA-Project
Properties. Activate the Protection tab, and set password on your
VBA-Project. Close VBA editor.

Now user sees in formulas references to sheet PI, where are dislocated only
data available on inpust sheet too, and there is no way to get formulas from
PI visible without some crackering. A common user can't make the sheet PI
visible without password (the sheet PI isn't displayed in hidden sheet's
list, when you select Format.Sheet.Unhide), and can't activate VBA editor,
where it's possible or where he could use VBA to read this information.

 

[Max]

I'm out of ideas to suggest, sorry.

It's always tough to keep up with "dynamic" specs <g>
(a.k.a. "moving targets" in management lingo)

Hope Arvi's suggestion will work for you.

 

[Mike A]

This is very difficult to hide in the same file - even if you name the
sheet 'Confidential' and set its visible property to VeryHidden in the
VBA editor, anyone can make a new sheet, type =Confidential!A1 in cell
A1, and autofill the sheet. They will have an exact copy of values on
the hidden sheet!

You may want to put the sensitive data in an Access database file
(with a password) and have Excel look up the info you need using an
ODBC connection. The 'public' values can be kept in the spreadsheet
and the private info will be protected with a password, and it will be
in a separate file.

-Mike

I have a very sensitive Excel workbook with Salary Rating Info. The
formulas do lookups and other calculations against hidden worksheets
within the workbook. I do not want people to see the worksheet
references at all in the formula bar of the cell they are in. How can
i keep these formulas working without letting users see what worksheet
the formula refers to? Thanks

Mike Argy
Custom Office Solutions
and Windows/UNIX applications

Please post on-topic responses to the newsgroup

To e-mail me, remove nospam from the address in the headers