Protecting Xcel worksheets

Hi All,

I want to protect and hide formulas for a whole array of cells in 
spreadsheet ... Is there a way of doing this but retaining the ability to 
apply the Autofilter (under 'Data' menu option)?

Many thax,

Don-
0
Utf
4/1/2010 1:13:01 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
499 Views

Similar Articles

[PageSpeed] 35

First you must have autofilter enabled then when protecting the sheet, allow
"use autofilter" in options.


Gord Dibben  MS Excel MVP

On Thu, 1 Apr 2010 06:13:01 -0700, Don <Don@discussions.microsoft.com>
wrote:

>Hi All,
>
>I want to protect and hide formulas for a whole array of cells in 
>spreadsheet ... Is there a way of doing this but retaining the ability to 
>apply the Autofilter (under 'Data' menu option)?
>
>Many thax,
>
>Don-

0
Gord
4/1/2010 2:13:56 PM
Many thanks Gord! Worked a treat ;)!

"Gord Dibben" wrote:

> First you must have autofilter enabled then when protecting the sheet, allow
> "use autofilter" in options.
> 
> 
> Gord Dibben  MS Excel MVP
> 
> On Thu, 1 Apr 2010 06:13:01 -0700, Don <Don@discussions.microsoft.com>
> wrote:
> 
> >Hi All,
> >
> >I want to protect and hide formulas for a whole array of cells in 
> >spreadsheet ... Is there a way of doing this but retaining the ability to 
> >apply the Autofilter (under 'Data' menu option)?
> >
> >Many thax,
> >
> >Don-
> 
> .
> 
0
Utf
4/1/2010 3:08:01 PM
Reply:

Similar Artilces:

one chart, two worksheets
I have a worksheet with 2010 data and one with 2011 data. I'd like to make one chart where I can compare the 2 years of data. Can someone help? Thanks, Pat ...

Conditional Formatting referencing other Worksheets
Hello, I sometimes get lost with too much formula-ese, so please forgive me if the answer is clearly out there. First, let me state that I understand that when a cell is linked, just the value, not the format travels with it. Here is what I want to do. I have a "dashboard" worksheet that combines the tasks off of 5 other worksheets. Basically I want to set up formatting so if the cell references: 'wksht1'=blue 'wksht2'=pink 'wksht3'=yellow ....and so on... What is the simplest way to do this? From my research on the boards it looks like conditional format...

Saving only updated cells or worksheets
(This is the first time I'm using this feature - hopefully I'm following the proper procedure!) Is there a way, in Excel 2002, to only save the cells or worksheets that have been updated, instead of saving the entire file each time? I have a large file, with VBA code, and is I could set it to only save the updated cells or worksheets, this would decrease the time needed to save the file. I read about Tools, Options and Allow Fast Saves, but when I follow that path, there is no such option to Allow Fast Saves. Any assistance would be greatly appreciated! Thank You, MWS I am ...

Question About Importing Columns From A Datasheet to A Worksheet
I am asking for any help you can give me regarding the best way to import columns from a datasheet into another worksheet. I have many worksheets to construct but I will need to include only a few select columns from the datasheet on each worksheet. Each worksheet will include a different assortment of columns. It is my goal to only continuously update the datasheet information, so all the changes will carry over to all the worksheets without me having to manually update every sheet. I know how to do this function per cell, but I cant figure out how to do it by column. Please...

i can't get outlook to import info from the worksheet it created
I created an Excel worksheet from a contact list in outlook by exporting it. When I add contacts to the same Excel worksheet, outlook won't import the new data. What am I doing wrong? DId you expand the named range on the worksheet to cover the new data?=20 --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx =20 "oaksie1" <oaksie1@discussions.mic...

New worksheet #2
How do you you give your workheet a title, e.g. 'Prodect Sales' in the middle then the cells below as usual? Cherie; Right click on the tab you wish to rename and select Rename. The words will become "negatived" like a picture. Type the new name of the sheet. God Bless Frank Pytel "Cherie" wrote: > How do you you give your workheet a title, e.g. 'Prodect Sales' in the middle > then the cells below as usual? Hi Cherie I read your question a little different to Frank. Select a range of cells say A1:H1 then Format>Cells>Alignment>Hori...

Excel worksheets auto-incremeting question
If you have time, I do need some help on an Excel issue. Let me describe what I need to happen in Excel. I will build an Excel workbook with a number of specific worksheets. The first worksheet of the workbook will only be a place to enter or download information over and over. Each time new information is entered in the various cells, the old information should be overwritten. The various cells in the first work sheet relate to cells in the other worksheets in the workbook. What I need to happen is for these cells in the other worksheets to populate each time but to increment to...

How do I combine two worksheets into one graph
I have two worksheets on an Excel spreadsheet. I would like to take both worksheets and place in one graph. Any suggestions? Make a chart with data from one sheet. Then copy the data on the other sheet, select the chart, and use Paste Special from the Edit menu to add the copied data as New Series. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ McPowerUser wrote: > I have two worksheets on an Excel spreadsheet. I would like to take both > worksheets and place in one graph. Any suggestions? ...

command buttons disappear when I move worksheet 2007
Excel 2007 - When I move my worksheet containg command buttons that run VB code to another workbook, the buttons disappear. How can I make the buttons move with the worksheet? I've never had this happen, but I use xl2003 much more than xl2007. You may want to give details of how you did the move. moulage wrote: > > Excel 2007 - When I move my worksheet containg command buttons that run VB > code to another workbook, the buttons disappear. How can I make the buttons > move with the worksheet? -- Dave Peterson ...

Why can't I enter any data on any new worksheets?
I cannot enter any information on any new, or existing worksheets. I've even tried uninstalling and reinstalling the program. Please help! ...

How to protect data in rows from being seperated during sort
I have a huge spreadsheet that needs to be sorted a million times but several people. The problem is that during some of the sorts they don't always grab the whole row and the data then gets scrambled. how can I protect the sheet so that the rows can be moved and sorted and edited but the data within the rows always stays together If your sheet is not too dynamic, and tends to stay the same size, then simply create a named range. Select *all* the rows and columns concerned. Click in the "Name box", and enter a short appropriate name, like "sort" (no quotes), an...

Excluding worksheets from printing
Hi, I currently embed an Excel macro in my spreadsheets which will print the entire workbook (40+ sheets) should the user request it: Private Sub CommandButton2_Click() Dim Sheet As Worksheet Dim lAnswer As Long lAnswer = MsgBox("This report contains " & Sheets.Count & " sheets - Do you want to print them all?", vbYesNo, "Print?") If lAnswer = vbNo Then Exit Sub Else Worksheets.Select Application.Dialogs(xlDialogPrint).Show Sheets("Total").Select End If End Sub However, I have now incorporated several workings sheets that a...

protect file from getting deleted
I have made a file in Excel 97. I want to protect it in such a way that nobody should be able to delete it. Nobody should also be able to save another file on it. Can anyone help please There is no way that you can with 100% certaintly prevent a file from being deleted or overwritten. This is why the first three rules of computing are backup, backup and backup. When you are done working on a file, copy it from your hard drive to what ever else you may have access to. IE,a second hard drive, server drive, floppy, CDR, zip disk, USB Key drive. If you don't have access to a...

cut and paste to different worksheet
Hi > Experts > I am working on excel sheet to make a daily production report. In my > worksheet, there are 300 rows selected for a week and column from A to W. Column F is named as "STATUS". When a particular job finishes, person on the section puts C (C means Completed) in the column F. What I am after is as soon as column F (Status) goes C, then information for that job from column A to column W should get copied automatically to another worksheet 3. consider in one day 10 jobs gets completed onto a section then 10 jobs infomation from column A to column W s...

universal formatting of all cells in all worksheets
I have 140 sheets in an excel document. In column AF I want the width to be set to 15.86. Instead of going to every sheet and making the change, is there a way that I can set the width for that column in all of the sheets right click on a sheet tab>select all sheets>set whatever on the sheet you are on>select any One sheet. -- Don Guillett SalesAid Software Granite Shoals, TX donaldb@281.com "jeremyrod" <jeremyrodriguez@cmsmechanical.com> wrote in message news:0b4301c351e9$39fe41e0$a501280a@phx.gbl... > I have 140 sheets in an excel document. In column AF I >...

Single worksheet, multiple pages?
Hello, I have a single excel worksheet, and due to area's of the sheet I do not want to have printed, (the data is to be printed onto pre printed forms), have used the set print area option to exclude those area's. The problem is now I have multiple pages inside a single worksheet. Each page prints seperatly, so I end up with 5 pages with only a small amount of the full worksheet on each. Thanks in advance Peter Hi peter i'm not sure what you're problem is - sounds like you've set it up and got it working like you want - how would you like it to be different? chee...

Insert Existing Worksheets into a new Workbook
I have a set of many single page worksheets with calculations; I want to make a single Workbook using all of those pages. Then I want to extract data from a certain cell on each Worksheet and SUM that data on the last page of the Workbook. In EXCEL 2007 in order to extract and sum data on a specific page of the Workbook:- 1. Assume I have a Workbook set up in which there are 4 Worksheets and I wish to sum cell C6 from 3 of those Worksheets in Sheet1. 2. In cell C6 of Sheet1 (this is where I want the total to appear):- Home / Editing group / AutoSum / 3. Now click on ...

is it possible to Hide certain worksheets from certain users?
is it possible to Hide certain worksheets from certain users? if Yes which version of Excel offers this option? there is no built-in facility, you would have to build it all (and it is no simple). -- HTH RP (remove nothere from the email address if mailing direct) "Admin" <Admin@discussions.microsoft.com> wrote in message news:E10B3C1B-9E53-4927-BE3B-BE5C1AFFA223@microsoft.com... > is it possible to Hide certain worksheets from certain users? > > if Yes which version of Excel offers this option? > It requires using macros, which may or may not work for y...

Protecting Selected Cells and Functions
I have a worksheet. In Cell B2 is a Data validation box Listing a range of colleagues names( DRop Down Menu). On selection of a name in B2, the contents of the whole worksheet changes. I like to Protect the worksheet for: 1) Hiding the formulaes 2) And most importantly preventing editing of the contents of any other cell (except B2). and yet be permiitted to: 3) Select contents in Cell B2 (Data Validation Box) 4) Select Auto filters in Row 4 I've tried using the the Tools/ Protect worksheet menu, ticking Select Lock Cells, Select Unlock cell, use auto filters. And in in Format/Cells/...

Is there an "Execute" worksheet function?
Is there a way I can get Excel to "execute" an expression that is stored in a cell as a test string? Say C4 contains "3+4". Can I put some expression in C5 that will execute that expression and result in "7"? I tried =calculate(C4) and a few other things, but no joy. in the absence of putting = in front of c4, try this =LEFT(C4,FIND("+",C4)-1)+RIGHT(C4,LEN(C4)-FIND("+",C4)) -- Don Guillett Excel MVP SalesAid Software dguillett1@austin.rr.com "LurfysMa" <invalid@invalid.invalid> wrote in message news:o5fn83thi0g5tivf55abvhv...

Continuous page numbers in workbook of multiple worksheets
I would like the page numbers to start with 1 on the first worksheet in a workbook and then have the next worksheet pick up with where the first worksheet left off. For example, if the first worksheet has 2 pages and the second worksheet in the workbook had 3 pages, then the first worksheet would contain pages 1 and 2 while the 2nd worksheet would contain pages 3, 4, and 5. Is there a way to have Excel figure out what the first page number should be in the 2nd worksheet or do I always have to manually update it in Page setup. THANKS! Hi Val Select all sheets first (right click on a...

want to limit worksheet to 1000 rows instead of 65,000
My worksheets only need 1000 rows but the worksheet remains at 65,000+ rows. I have 1 GB or ram and 6 worksheets causes problems. How can I set a default to limit the max number of rows and columns. Thank you All (pre-XL07) worksheets have 65536 rows. That can't be changed. However, unused rows don't take up any memory or disk space, so there's no need to try to limit them. What problems are you having? In article <1C30E096-94AD-4244-A976-FEE04B45912D@microsoft.com>, GeoObject <GeoObject@discussions.microsoft.com> wrote: > My worksheets only need 1000 rows b...

FORECASTING SALES (please let this be a worksheet function)
Hi I have recently been given the task by my boss to forecast one of ou key customers monthly usage until monthly until end of 2006, I am starting with this customers indidual branches monthly usage fo the past 3 years, Some branches will have opened and closed during thi time, And i am looking to be able to predict monthly usage for the nex 18 months Has anyone got any idea's on the best way to forcast within excel, I a currently using trend (fomulae given to me on this sight) but people ar saying this is not the best way Exponentionally has been mentioned by a few of my collegues but...

how to protect appointed rows or columns to use the mouse and keyboard
hzh, You could include some text in your post. Maybe then we could figure out what you mean by "appointed"?? "To use the mouse and keyboard" makes absolutely no sense whatsoever. Maybe you're a new poster??? If so, take a look here before reposting: http://www.cpearson.com/excel/newposte.htm Most anything that could be asked in the newsgroups has already been answered. Take a look here and you may just find what you're looking for (whatever that is)?? http://tinyurl.com/29pby John "hzh" <hzh62@163.com> wrote in message news:7A2382EE-00E6-4679...

Summing instances of text across worksheets
Hello, I have a workbook with separate sheets for each month. The sheets contain a list client names (entered exactly the same on each sheet). I need to summarize how many times a client name appears in total. For example, John Brown may appear Jan & Feb (=2) where Mary Brown may appear Jul, Sep, Dec (=3). I've been trying pivot tables without luck. Hoping someone can help. Thanks in advance! Never mind, I consolidated all the worksheet data into one and the pivot table works fine. Thanks anwyay! "George" wrote: > Hello, I have a workbook with separate sheets fo...