Preventing Editors from Deleting/Reordering Rows

I need some users to have Editor access to be able to enter data into the sheet, but aside from training and a clear job aid, I want them to not delete rows or sort rows.

I have a column that generates a unique code for a course in a term. The first course entered for that term has a code that ends with 01 (e.g., NC60001), the next one has 02 (NC60002), etc. If a course is entered but for a new term, the code ends in 01 (NC90001). The "6" and "9" indicate the term when the course is offered.

If the rows are reordered or deleted, what was the say the second course in the term may become the first course, and the unique code changes from say NC60002 to NC60001. This has an impact in other sheets that use the unique code for payments and instructor scheduling.

I am already using the auto-number feature on the sheet, so I can't use it to generate this unique code.

Aside from training and the job aid, is there any way to prevent an Editor from deleting or sorting rows?

Is there another way to generate this unique code without the auto-number or dependence on the order of the rows?

Here is the formula I use for the unique code:

=IFERROR("NC" + IF(Term@row = "Spring", "1", IF(Term@row = "Summer", "6", IF(Term@row = "Fall", "9", "0"))) + RIGHT("0000" + [H-CRN (Helper)]@row , 4), "")

The formula for [H-CRN (Helper)]

=IFERROR(COUNTIFS(Term$1:Term@row , Term@row , [Fiscal Year]$1:[Fiscal Year]@row , [Fiscal Year]@row ), "")

This second formula generates a number of the order that course is added on the sheet, by Term and Fiscal Year. Like I mentioned earlier, the first one in a term of a Fiscal Year returns 1, the second returns 2, etc. The numbering starts over when a different Term and Fiscal Year is added. Yes, this means a unique code like NC10001 is repeated at the start of the same term in each fiscal year, but that's OK because we use that unique code and a "term code" together to uniquely identify the course.

NC10001-202601 (first course in sheet for FY 2026 in 01 or "spring")

NC10001-202701 (first course in sheet for FY 2027 in 01 or "spring")

NC60005-202606 (fifth course in sheet for FY 2026 in 06 or "summer")

Find more posts tagged with

Accepted answers

All comments

HLChristiansen

This is a great case for using Dynamic View, if you have that available. I recently changed to this with our team because one of the users selected a row and hit backspace, erasing the entire row. I was able to get it back thankfully, but it was a pain.

I also setup the Dynamic View to embed into a dashboard, so they can work directly from the dashboard, and have all their other links right in front of them for easier workflows. You can embed dynamic views using the Web content widget.

If these editors are adding data to rows, this would be my go-to. If they only need to add rows, I would create a form for them to do that.

One thing to be aware of with dynamic view is that nothing shows nested, we got around this with a helper column for parent/child rows, and conditional formatting. The formula for the helper column is

=COUNT(ANCESTORS())

Parent rows = 0

Child rows = 1

Grandchild rows = 2 (and so on)

You can then easily use the conditional formatting with the helper row to differentiate with color, bold etc.

Adrian Mandile CHESS

Hi @HLChristiansen,

A great idea on using DynamicView and also ways to simulate a Hierarchy using Conditional Formatting.

I use a 'Level' helper column in every sheet with row hierarchies and can recommend a slightly better formula, whereby only summary rows have a number and so all 'bottom' level rows, regardless of their hierarchy position, have no numbers and can therefore be treated the same. Try:

=IF( COUNT( CHILDREN() ) = 0, "", COUNT( ANCESTORS() ) + 1)

This will make top level 1 and any/all bottom rows blank ("").

It is easy to then use 'Level' in a column formula to behave differently =IF (ISNUMBER( Level@row) ), …

Paul Newcome

I also suggest Dynamic View if you have it available. If you do not have it available, you can use a two sheet system to lock in the course id.

The basic idea is that you have the id generated on this first sheet the same way you already are, but then you set up a second sheet and copy rows from the first to the second once they are ready for input. If you remove the formula from the id column on the second sheet, the id will come over as static data.

Users would work in this second sheet with static ids so that sorting, moving, archiving, deleting, etc. does not impact the course id.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.