1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Functions - urgent

Discussion in 'Business Applications' started by Lizhusain, Dec 3, 2002.

Thread Status:
Not open for further replies.
Advertisement
  1. Lizhusain

    Lizhusain Thread Starter

    Joined:
    Sep 13, 2002
    Messages:
    190
    hi:

    How are you?

    I need to protect some information in excel worksheet where no one can see except me.

    e.g the names columns. First names and Last names (2 columns)

    I know i can protect the entire worksheet, where the contents of the first names andlast names will be visible , but can't be changed and also i can hide those columns where no one can see them and protect the worksheet. The problem is if i copy the entire worksheet and paste it in a new file, then they can be unhid.

    What I need is just to protect two columns where no one, I mean no one except me can have access to them, be it through copy and paste or unhiding, Is this possible in excel?
     
  2. Yankee Rose

    Yankee Rose

    Joined:
    Jul 14, 1999
    Messages:
    1,861
    Hi Lizzy:

    I'm not an Office Guru (I'm sure somebody else might have a better way of doing what you want to do!) :) but take a look at this link:

    http://www.csun.edu/~tpurtee/protecting_your_data.htm

    Partway down the page it explains how to password protect individual cells or range of cells, noting you can change cell color to "hide".

    Hope that helps! Good luck!
     
  3. Lizhusain

    Lizhusain Thread Starter

    Joined:
    Sep 13, 2002
    Messages:
    190
    I am sorry I don't think that can work.

    What i want is for the cells not to be viewable, and access is denied except by me........some password or something.

    not the entire worksheet, just those cells.
    And I don't want them to see the contents either.
     
  4. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Liz:

    You'll want to put those columns on a separate worksheet, and hide the worksheet instead.

    That way, people are hardly even aware that the worksheet is there--unlike hidden columns.

    When you hide that sheet and protect the workbook, you should be good to go.

    Oh...and also, if you CUT the two columns and then paste them to the other worksheet, any cells that are referring to those columns ought to be able to still refer to them, but in their new location.
     
  5. Lizhusain

    Lizhusain Thread Starter

    Joined:
    Sep 13, 2002
    Messages:
    190
    mannnnnnnnnnnnnnnn i didn't want to move the contents.......bec. it has to be updated by someone else and sent back to me...........if they insert, then my worksheet will not be correct.
     
  6. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Perhaps you need to do this, then:

    Put formulas in a different workbook altogether.

    Copy cell C1 of your real workbook, go to a new one, and in cell A1, hit Edit-Paste special, Paste Link.

    Select and copy that cell until all your data is linked.

    If you get cells that have 0 in them, it's because there's no data in that cell in your original. If your original MIGHT ever have data in that cell, then you'll need the link.

    You can then do a Tools-Options-View and tell it you don't want to see zero values....

    If that won't work for you, start thinking about using Access.
     
  7. itsmeitsmeitsme

    itsmeitsmeitsme

    Joined:
    Apr 29, 2002
    Messages:
    789
    you can hide and protect just a selected # of cells......

    1. select the whole worksheet
    2.chose format,cells,to open the format cells dialog box
    3.click the protection tab and deselect the locked option(deselecting the locked option allows you to select a specific range or cell and then lock it)
    4. after making your selections hit ok
    5. highlight the cells you want to hide
    6. hide cells by moving the columns with your mouse(this is done by holding the mouse at the column dividers and dragging)
    7. Go ack to format cells and check locked
    8. go to tools ,protection and select protect sheet
    9. enter your password(2 times)
    now you are the only one that can get back into the locked and hidden cells but anyone else can enter data in all other cells.
     
  8. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    You know, Liz, after hearing itsme's reply to you, I went back and re-read this thread.

    Are you SURE you can copy a worksheet to a new book AND CHANGE IT when it is PASSWORD PROTECTED?

    I think not.

    I think Jody's answer should work for you after all.

    If your user's don't have the password, how can they change the sheet and unhide the columns?
     
  9. itsmeitsmeitsme

    itsmeitsmeitsme

    Joined:
    Apr 29, 2002
    Messages:
    789
    Your right dreamboat ....they cant unprotect without the password....even in a save as the password still exists.....
     
  10. Yankee Rose

    Yankee Rose

    Joined:
    Jul 14, 1999
    Messages:
    1,861
    :)

    Hope it works.
     
  11. Lizhusain

    Lizhusain Thread Starter

    Joined:
    Sep 13, 2002
    Messages:
    190
    nope not the worksheeet, those columns.......that I protect....
     
  12. Yankee Rose

    Yankee Rose

    Joined:
    Jul 14, 1999
    Messages:
    1,861
    Did you try what the link I previously suggested?

    Scroll down the page to where it says <b>Unlocking Individual Cells within a worksheet</b>. This site claims that you can lock specific cells (or a column, in your case) by following the directions provided.

    I guess I am not seeing why this will not work for you if you format the columns by changing the cell/text color to white so that only you could view it with a password.

    I am sorry if I am not understanding you. If this suggestion doesn't help, I hope someone else can help you. :)
     
  13. Lizhusain

    Lizhusain Thread Starter

    Joined:
    Sep 13, 2002
    Messages:
    190
    Hi: itsmeitsmeitsme
    what do u mean by hiding the cells, where do i move to?
     
  14. Lizhusain

    Lizhusain Thread Starter

    Joined:
    Sep 13, 2002
    Messages:
    190
    Hi Jody:

    If i format the cells to white font, then wheni click on that cell, the value or whatever is in there, will still show on the view bar. SOrry, i don't want them to see that info. Let 's put it this way, i don't want them to see what is in the cells, credit card info.
     
  15. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Okay, I'm fessing up. All this time I'm thinking...Liz is doing something wrong that this is not working for her.

    I PM her, and tell her to give me her email and I'll send her a workbook with a hidden column, blah, blah. (I'll show her! Right?)

    WRONG.

    I create the file and test it. Lo and behold, I can:

    -Copy to a different workbook and unhide the column
    -I can type in a link to the hidden cells and get the data

    :eek:

    Some Excel expert I am.

    Short of providing Liz with some code that'll automate copying and pasting ONLY the desired data to a new workbook for sending to others/allowing others to view, I have no answer.

    Liz: I apologize for being such a dork.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/106696

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice