Excel Help - Hiding/locking/protecting sheets

[Rizon]

I've googled this, but I couldn't find the answer I was looking for that worked (not sure if I was searching for the right thing).

I have an Excel 2007 Workbook that contains about a dozen worksheets. This workbook will be shared with others, however, it contains confidential data that's used in calculations visible in other worksheets of the w-book.

1) One set of confidential data is stored in it's own worksheet in the w-book. I don't want this worksheet visible to others ... at all. How can I restrict/lock/hide it so that they can't unhide or view it?

2) I have a group of cells in another worksheet I want restricted/locked/hidden as well. The rest of the worksheet contains non-confidential data that I want to leave visible.

I tried hiding/locking/protecting, but I was easily able to remove those restrictions in read-only (or I did something wrong).

[DaddyTorgo]

I'll presume there's a reason why you don't want to just copy and paste-special+values the stuff you want people to see and delete the rest, right?

[Rizon]

Originally Posted by DaddyTorgo

I'll presume there's a reason why you don't want to just copy and paste-special+values the stuff you want people to see and delete the rest, right?

Yeah, I may be changing them several times later. And laziness.
But really, the data that's non confidential is 28 columns and 200 rows of solid data. The confidential part of that is only 6 rows (28 columns) which is in the middle.

[wade moore]

These other people just see the summary sheet?

Why not make a separate file altogether that references your original file and the values in the summary sheet?

So you have File A (with the calculations). The Summary sheet is "Summary".

Then you have File B. In File B you have A1 is ='[Summary.xlsx]Summary'!$A$1

A2 is ='[Summary.xlsx]Summary'!$A$2

etc, etc?

Then you can lock down the file with the summary data?

[DaddyTorgo]

that's fuckin advanced excel mumbo-jumbo there. I don't even know how to do that shit. Go for it.

[Rizon]

Originally Posted by wade moore

These other people just see the summary sheet?

Why not make a separate file altogether that references your original file and the values in the summary sheet?

So you have File A (with the calculations). The Summary sheet is "Summary".

Then you have File B. In File B you have A1 is ='[Summary.xlsx]Summary'!$A$1

A2 is ='[Summary.xlsx]Summary'!$A$2

etc, etc?

Then you can lock down the file with the summary data?

Thought about that, but there are about 195 linked cells from that worksheet. I thought it would be easier to just lock the sheet down instead of connect different workbooks.

[Suburban Rhythm]

Maybe I am just missing something here but...

Right clicking the tab(s) in 2007 should give you options, among them hiding and protecting

[johnnyshaka]

Originally Posted by Suburban Rhythm

Maybe I am just missing something here but...

Right clicking the tab(s) in 2007 should give you options, among them hiding and protecting

Problem is that anybody can "unhide" a hidden worksheet whether it's protected or not.

[Rizon]

Originally Posted by Suburban Rhythm

Maybe I am just missing something here but...

Right clicking the tab(s) in 2007 should give you options, among them hiding and protecting

Originally Posted by johnnyshaka

Problem is that anybody can "unhide" a hidden worksheet whether it's protected or not.

Yeah, that was my problem. I tried different combinations of stuff I found online, but for every one I was too easily able to unhide or unlock stuff, even without the password.

[johnnyshaka]

Originally Posted by Rizon

Thought about that, but there are about 195 linked cells from that worksheet. I thought it would be easier to just lock the sheet down instead of connect different workbooks.

What about filling the cell with the same colour as your font to "hide" the contents of the cell? If the worksheet is protected and you don't give them any access at all they won't be able to see what's there even if they click on the cells.

EDIT: or changing the font to white so it looks empty?

[Suburban Rhythm]

Originally Posted by johnnyshaka

Problem is that anybody can "unhide" a hidden worksheet whether it's protected or not.

True. Only other option I can come up with is right click, move or copy, choose move to new book in drop down, send this version. Pain in the ass, will need to send this way each time, but links remain to old sheet. Can update original which only he can access.

[FrogMan]

think I got an idea... From the Excel help file

Display or hide all values in a cell

1. Select the cells that contain hidden values or values you want to hide.
2. On the Format menu, click Cells, and then click the Number tab.
3. Hide all values in a cell
   1. In the Category list, click Custom.
   2. In the Type box, select the existing codes and press BACKSPACE.
   3. In the Type box, type ;;; (three semicolons).

then to make sure the value doesn't appear in the formula bar, go to the protection tab in cells format and make sure the cells are both "locked" and "hidden". Then go to Tools, Protection, Protect sheet and add a password. It just worked for me...

To show the hidden cells, you unprotect the sheet, then select the cells with hidden value and pick the general format for number.

FM

[FrogMan]

Originally Posted by johnnyshaka

What about filling the cell with the same colour as your font to "hide" the contents of the cell? If the worksheet is protected and you don't give them any access at all they won't be able to see what's there even if they click on the cells.

EDIT: or changing the font to white so it looks empty?

tried that too and hiding the cells and then protecting the sheet but I was still able to highlight the cells and the values would show in reverse selection (the gray)

FM

[Passacaglia]

Might as well just make a new copy of the file, and delete it. Are you linking to the confidential data? If so, make sure you break the links first.

[Rizon]

I might just do what I did last year .... create a new copy of the file, cut and paste values for the big chunks of data, then delete the confidential stuff since it's no longer tied with formulas.

[Passacaglia]

Why cut and paste? If it's a copy of the file, just keep everything where it is, and break the links.

[johnnyshaka]

Originally Posted by FrogMan

tried that too and hiding the cells and then protecting the sheet but I was still able to highlight the cells and the values would show in reverse selection (the gray)

FM

When you protect the sheet are you unchecking the "select locked cells" and "select unlocked cells"? Because you shouldn't be able to select, and therefore see the contents, of a locked cell if you've unchecked those.

[Rizon]

Originally Posted by Passacaglia

Why cut and paste? If it's a copy of the file, just keep everything where it is, and break the links.

Wouldn't that kill the results of the formula?

Basically it's salaries and benefits. I can give a total of them, just not show what each individual earns.

[Suburban Rhythm]

Originally Posted by Rizon

Wouldn't that kill the results of the formula?

Basically it's salaries and benefits. I can give a total of them, just not show what each individual earns.

Copy/Paste Special Values on the copy, then delete everything you don't want seen

[FrogMan]

Originally Posted by johnnyshaka

When you protect the sheet are you unchecking the "select locked cells" and "select unlocked cells"? Because you shouldn't be able to select, and therefore see the contents, of a locked cell if you've unchecked those.

oh, you are right, that should do the trick. change the font color of the cells to be hidden to white, or whatever the background of said cells is, then check the cells to be "locked" and "hidden" and then protect the worksheet with a password making sure to uncheck at least the "select locked cells" checkbox to keep people from seeing what's written in white in the cells...

Fairly painless...

FM