You may have summary data in the header rows on your main spreadsheet tab or you may have a summary tab in your main spreadsheet. When the structure of summary data is more detailed, e.g., department level data that may be shown/hidden, it may be built on a summary tab. If the structure of summary data is at a high level, it may make sense to keep it in header rows on the main spreadsheet tab. Views may be created over summary data on the main spreadsheet tab or on a separate summary tab, and then shared to the users who need access to summary data.

Set Up a Summary View on a Summary Tab

As long as all of the information on a summary tab is built as formulas (that reference your main data tab), you can create views of the summary data that assume the security that you designed in the views for your main tab.  

On the Summary tab:

  • Create a view that you want to share with managers on the Summary tab.

View Name:  .Budget Summary

  1. Summary Calcs: syntax:  A1=THIS;B1="#sheetview:Associate Detail***.My Whole Org#";G1="#disablescrolling#";H1="#hideheaderrows,E1:E32#"

Where:

  1. Associate Detail is the exact tab name on the main tab
  2. .My Whole Org is the exact view name of the view on the main tab that the security references for this view on the Summary tab
  3. disablescrolling removes scrolling from the view and allows the calculations to be made across all of the summary rows.
  4. hideheaderrows encompasses the length of the data set and references the column where Show logic is built (i.e., if you want rows to be hidden based on the values that will return according to the logic of the view you are referencing on the main tab. For example, if a Summary row will return a zero value, you may choose to hide that row)
  1. Set Freeze at Row: to the last row of data on the Summary tab
  2. Set an Advanced Filter to accommodate the logic built in hideheaderrows, e.g., Column E filter set to 1 = Show UnLocked


  • On the Summary tab, build formula logic into the "Show" column to hideheaderrows as needed.
  • Users will need to be shared to the Summary tab as needed.

Set Up a Summary View of Header Rows on the Main Tab

When summary information is at a higher level, sometimes it remains in a table(s) in the header rows in your main tab. You may create views of the summary data that suppress the detail rows so users see just the summary information when they access the summary view. Sometimes this summary data is in header rows to the right of the data set such that they are out of the way of the data; they can be anywhere in the header rows.

On the Main tab:

  • Create a Summary view that you want to share with managers.

View Name:  .Summary

  1. Summary Calcs: syntax:  A1=THIS;AM1="#disablescrolling#";AN1="suppressdetailrows"

Where:

  1. disablescrolling removes scrolling from the view
  2. suppressdetailrows hides the detail rows
  3. AM1 and AN1 are varialbe; these are cells that are hidden in the view. Use any cell that will not display to the end user, that does not already contain a value or a formula, and that is before the last three columns in the SecureSheet (the audit columns).
  1. Set Freeze at Row: to your header row (the row with all of your column headings).
  2. Set all columns that do not contain summary calculations to Hidden.
  3. Set all columns that do contain summary calculations to Locked.


  • Users will need to be shared to the Summary view as needed.