Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I had asked this question before but I am unable to execute the answer correctly or I'm missing a step or two.

    I have a spreadsheet where I have a number of formulas. I paste in data from another source and the formulas do their job.

    I want other people to use this spread sheet but I want to safeguard the integrity of the formulas by protecting only the columns with the formulas. I also do not want these formulas to show in the formula bar. I only want the values to appear.

    The spread sheet still has to be able to be sorted.

    Is this asking to much or can one protect and show only values not the formulas in the cells.

    Thanks,

    MNN

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You can hide formulas in a protected sheet by setting the formula cells protection to both Locked and Hidden.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    I think you may be unlucky on this outside of a macro in XP and 2003 (not checked 2007/2010 yet)
    As far as I know, when you Protect a sheet, even if you tick allow sorting and allow filters,
    all the cells in the Sort Range must be unlocked for excel to allow the sorting.
    If some are locked and some unlocked, then sort is disabled, even if the sort option on protect is ticked.

    To my mind, that is a bit silly from the MS point of view, and also misleading.
    Of course I could be wrong.

    The normal work around is to use a macro to un-protect, sort then protect,
    but this is right pain in a large sheet with lots of columns.
    Andrew

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    MNN,

    Another tact you might try if the sheet with the formulas {we'll call it Formulas} is fairly static is to create another sheet {call it Report} that is setup like the one with the formulas using Fixed References, i.e. Formulas!$A$1 to the formulas on the Formulas sheet. By doing this you could leave this sheet unprotected since all the user would see is the value calculated on the sheet with the formulas which are hidden and protected. The new sheet would be fully sortable.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •