Hiding Formulas

[Wins07]

I need to protect formulas entered into cells which are summed up into a
subtotal row and grouped/outlined for presentation. As i apply the protect
worksheet, I can no longer expand or summarise presentation.

Does anyone have a solution as i do not want my formulas to be overriden and
need to be able to let users view both summarised and expanded results.

worksheet is as follows:

Expense Type Jan Feb Mar Apr

Marketing 200 250 200 300
promos 40 90 80 100
entertainment 60 60 70 50
advert 100 100 50 150

Office Expenses 300 300 300 300
stationery 100 100 100 100
photocopy 150 150 150 150
postage 50 50 50 50

Salary and Wages 600 650 650 600
Basic Salary 500 500 500 500
Overtime 50 100 100 50
travelling 50 50 50 50


The figures are calculated from a list on another sheet within the workbook
where data are usually entered without requiring any protection.

 

[bj]

When formulas are sensitive, I have just copied all the data and formats to a
new book with values only.and gave this book out.

 

[Bill Kuunders]

You could have two seperate sheets for the results.
One expanded and one that takes just the totals.

Or have a macro for summarised view that will unprotect the sheet do the
summary view and protect the sheet again
And another macro that will unprotect do the expanded view and protect
again.

Macro's to be assigned to buttons in an area that will not be hidden when in
summarised view.

Use the macro recorder to get some idea what the code will look like.

In my trial example
To show the expanded view.......(show detail .....true)


Sub Macro2()
ActiveSheet.Unprotect
Range("A1:A12").Select
ExecuteExcel4Macro "SHOW.DETAIL(1,11,TRUE,,8)"
ExecuteExcel4Macro "SHOW.DETAIL(1,6,TRUE,,3)"
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
End Sub

Range A1:A12 should be a named range so that the macro will be able to keep
track of the area.

Good Luck