Two issue re auto-filter on protected sheets

[jasmith4]

I have a big spreadsheet with macros that crunch some numbers and put out results in a new workbook: the workbook is protected, and so are all sheets, but with sorting and filtering allowed. The headers have auto-filter buttons.

Issue #1: the auto-filter allows the user to filter, but upon sorting the user gets the "cell is protected" dialog box, and it doesn't sort.

Also, above the headers there are cells which have SUM formulas operating on the columns below them. I'd like them to sum only the displayed cells when a user filters. I wish the CELL function had "height" as well as "width" among its choices, because then I could create a hidden column of formulas =0<CELL("height",$A2), etc., and use SUMIF on that column.

Issue #2; without VBA code, is there a way to determine if a row is hidden or filtered out? Or how would I accomplish the above?

 

[jasmith4]

Somehow the above got garbled a little. Here's the original:

I have a big spreadsheet with macros that crunch some numbers and put out results in a new workbook: the workbook is protected, and so are all sheets, but with sorting and filtering allowed. The headers have auto-filter buttons.

Issue #1: the auto-filter allows the user to filter, but upon sorting the user gets the "cell is protected" dialog box, and it doesn't sort.

Also, above the headers there are cells which have SUM formulas operating on the columns below them. I'd like them to sum only the displayed cells when a user filters. I wish the CELL function had "height" as well as "width" among its choices, because then I could create a hidden column of formulas =CELL("height",$A3), etc., and use a SUMIF formula that checks for non-zeros in the column.<CELL("HEIGHT",$A2), column.


Issue #2; without <ACRONYM title="visual basic for applications">VBA</ACRONYM> code, is there a way to determine if a row is hidden or filtered out? Or how would I accomplish the above?