1

A budget I am working on has each month on the same sheet and I would like to control which months are visible when comparing expenditure and income. In order to do so, I grouped the rows and columns according to category and month. The only cells which should be edited are the planned expense/income values. It makes sense to protect the sheet except for those cells. However, by protecting the sheet, I am unable to collapse or expand categories and months. Is there a workaround to retain the design and experience I would like to implement?

1

I ran into this same issue. I was unable to find a suitable solution to the problem for myself, however I did find a workaround that may work for you.

I am working on a project that requires protecting the spreadsheet except for select cells, where end users should not have the ability to edit protected cells without exception. This means strict protection (no warning) is the only option. What you can possibly use as a workaround is to change the protection settings to "Can edit (with warnings)". For these steps, I am assuming, as you said, that the entire sheet is protected except for specific cells.

Instructions:

  1. Open your Google Sheet.
  2. In the menu ribbon at the top click the "Data" button.
  3. In the drop-down menu for Data, click "Protected sheets and ranges".
  4. Select the Item on the sidebar that says "Protecting spreadsheet except for editable ranges: ..."
  5. Scroll to the bottom of the list of unprotected ranges.
  6. Click "Change permissions" in the bottom section under the Permissions header.
  7. Change the radio button to select "Show a warning when editing this range" instead of "Restrict who can edit this range".
  8. Click the "Done" button.
  9. Click the "Done" button in the sidebar.
  10. $$$

The only caveat here is that you will receive a warning every time you try to expand/collapse a row group (unless you check the box to disable warnings for 5 minutes, which I assume you do not want to do to avoid the risk of accidentally editing a protected range).

Hope this helps! Let me know if you figure a workaround for when the range is strictly protected.

  • I like your solution. I found that I was able to change the framework of the budget and create a search engine which queries the correct data determined by a date range. It requires a significant amount of formulae, but handles the workload well. – David E. Farmilant Nov 9 at 0:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.