Forum Discussion

rclanger's avatar
rclanger
Copper Contributor
Sep 24, 2022

Skip a cell when entering data

I want to skip calculated cells in the row. 

  • JonDK's avatar
    JonDK
    Copper Contributor

    I used Hans' method here; however, it has changed the way the tab and enter button affect the next cell entry. A 5x5 grid will be my example.

    Previously, I could start at A1 and tab down the row to A5, press enter, and the entry would go to B1.

    Now (after applying the sheet protection method above), I tab down to A5, press enter, and the selection drops to B5. 

     

    I am not Excel literate and I inherited these data entry workbooks from the previous employee. I'm trying to streamline my data entry method by blocking the curser from accessing irrelevant cells but also hoping to restore the cell selection process to what I'm accustomed to with the enter button.

    (Kinda feels like I'm having a 'want my cake and eat it too' problem)

    • By default, Tab moves to the right and Enter moves down.

      If you unlock A1:B5, and leave only the check box 'Select unlocked cells' selected when you protect the sheet, pressing Enter will move from A1 down to A2, ..., A5, then to B1, B2, ..., B5 and back to A1.

      • JonDK's avatar
        JonDK
        Copper Contributor

        My apologies, Hans. I was mistaken earlier:

        If I have a workable area A1:CT324 (the area of my actual sheet I want to apply this to)

        On row 1 data is entered in A1:BG1. When I press enter I would like the selection to return to A2. (This is the normal movement throughout an unprotected sheet.) But with a protected sheet, if my last entry was BG1 and I press enter, the selection drops to BG2.

        Is there any way to have the selection return to A2, A3, etc. when pressing enter while on a protected sheet? 

  • dscheikey's avatar
    dscheikey
    Bronze Contributor

    rclanger 

    You can allow only unprotected cells to be selected in a protected worksheet. This means that it is not possible to click on these cells. If you move the cursor over the column, it is skipped. I can't think of any other possibility besides sheet protection.

    See also my attached example.

     

  • rclanger 

    Select the cells where you (or the user) should be able to enter/edit data.

    Press Ctrl+1 to activate the Format Cells dialog.

    Activate the Protection tab.

    Clear the locked check box.

    Click OK.

     

    On the Review tab of the ribbon, click Protect Sheet.

    Clear the check box "Select locked cells" (but leave the check box "Select unlocked cells" ticked).

    Click OK.

     

    Pressing Tab or Enter will now move between the unlocked cells only.

Resources