Forum Discussion

intern2aacy's avatar
intern2aacy
Copper Contributor
Mar 04, 2024

Lock Different Columns for Different People

We have a spreadsheet where people at various work levels can contribute comments. However, we need to restrict what people at lower work levels can see in the comments:

 

Tutor Session Records    Family Specialist NotesCoordinator Notes
Date of Appointment (dd/mm/yy)Date Appointment Agreed (dd/mm/yy)Session Held (Y/N)Reason if not held
(pull-down list)		Tutor NotesFlag?Date of NoteNoteDate of NoteNote
2/15/242/20/24y Session went fine; student has trouble with English language 2/22/24We should conser ESL help for this student pending budget approval2/24Need to speak with Director for ESL funding. Student hasn't benefited from this before.

 

Tutor should only see Tutor Comments.

Family Specialist should see their comments and Tutor comments.

Coordinator should see all comments.

 

I can easily group the columns depending on access level. How can I limit access to the Column Group levels by Work Level?

 

Windows 11 Home

MS Office Professional 2019

I cannot attache a file; please see table above, for the relevant column headers.

 

 

excel

2 Replies

Sort By
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Mar 07, 2024

    intern2aacy 

    To restrict access to specific columns based on the work level of the user, you can use Excel's built-in features such as protection and hiding. Here's how you can achieve this:

    1. Protecting and Hiding Columns:

      • Group the columns based on access levels. For example, group Tutor Session Records columns together, Family Specialist Notes columns together, and Coordinator Notes columns together.
      • Then, protect the worksheet and apply different protection settings for each work level.

    2. Setting Worksheet Protection:

      • Select the entire worksheet by clicking on the square at the intersection of row numbers and column letters (top-left corner).
      • Right-click and choose "Format Cells."
      • Go to the "Protection" tab and uncheck the "Locked" option. This step ensures that all cells are unlocked by default.
      • Click "OK" to close the dialog box.
      • Now, select the columns that you want to restrict access to for each work level. You can do this by clicking on the column letters.
      • Right-click on the selected columns and choose "Format Cells."
      • Go to the "Protection" tab and check the "Locked" option. This step locks the selected columns.
      • Click "OK" to close the dialog box.

    3. Applying Protection:

      • Go to the "Review" tab on the ribbon.
      • Click on "Protect Sheet."
      • In the "Protect Sheet" dialog box, set a password if desired and choose the options you want to allow users to perform, such as selecting locked cells, formatting cells, etc.
      • Make sure to uncheck the "Select locked cells" option for columns that should be restricted.
      • Click "OK" to protect the sheet.

    4. Creating Different User Roles:

      • Determine the work level of each user (Tutor, Family Specialist, Coordinator).
      • Provide them with the appropriate password to unlock the protected sheet.

    5. Hiding Columns:

      • You can also hide columns that should not be visible to certain work levels.
      • Select the columns you want to hide.
      • Right-click and choose "Hide" from the context menu.

    By following these steps, you can restrict access to specific columns based on the work level of the user. Users will only be able to see and edit the columns that are unlocked and visible to their work level. The text was created with the help of AI.

    Note: Excel's worksheet protection features provide a basic level of security for preventing unauthorized users from making changes to certain parts of a worksheet.

     

    If the problem persists, you may need to give more informtions. In this link you will find how to give more information about it: Welcome to your Excel discussion space!

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

     

    Was the answer useful? Mark as best response and like it!

    This will help all forum participants.

Resources