protect cells while allowing user to filter in Excel

[mmaginniss]

I have an Excel master sheet which is linked to several other Excel sheets. On the master sheet I have a filter. I want users to be able to use the filter but not change the actual values in the cells of the master sheet. I have tried protecting the sheet but it doesn't allow me to then use the filter. Any ideas would be appreciated! Brgds.

 

[SkipVought]

What version of Excel are you running?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[mmaginniss]

Microsoft Excel 2000. Thanks.

 

[SkipVought]

I know that Excel 2002 has options on Protect Sheet that allows filtering. I don't believe that is available in Excel 2000. :-(

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[DaleWatson123321]

Hi mmaginniss,

A couple of suggestions...

1) Being new to Tek-Tips, you're likely not yet aware of the option (see bottom-left-corner of the Tek-Tips screen) - to "Click here to mark this thread for e-mail notification".

This will ensure that whenever someone responds to your posting(s), you'll become aware immediately.

You can also go into your "Personal Profile" (see the top-left-corner of the Tek-Tips screen) - and change your profile to set the e-mail notification to be automatic for all your postings.

2) Regarding your "master sheet" and the need to allow users to filter data while maintaining protection...

Have you considered setting up "macro buttons" for "specific filtering" - the result of which would be the extraction of a copy of the filtered data to a SEPARATE sheet. This way, you could maintain protection on your master sheet, and allow the users access to the data extracted to the SEPARATE sheet.

Alternatively, you could have a VBA routine that would extract ALL your master sheet data to your SEPARATE sheet, and turn on AutoFilter. Being a "copy", you would then not have to be concerned about Protection being disabled.

If you'd like assistance with extracting data (Advanced Filter) using VBA, I have some example files I can email you. These are "introductory" level files and should help in better understanding the Advanced Filter options.

Hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[mmaginniss]

Thx for the suggestions. I'll give them a try.