Positioning active cell when adding table rows.

[HighAndWilder]

I have, for example, a 5 column table with xlookup formulas in columns 4 and 5 that use the value in column 2 as the lookup value.

Column 1 contains a formula that just returns the row number of the data body range. =ROW()-1

Column 2 contains a value that is not referred to in a formula in the table.

What I would like to happen is that a user fills in the lookup value in column 2 and the value in column 3 and presses Enter to add a row to the table
and place the active cell in column 2 ready for the lookup value to be entered.

In Options - Advanced - Editing Options I have set the direction to Right when Enter is pressed.

I have to press Enter to advance through columns 4 and then 5 and then the active cell is in the next row in column 1.

I don't want users to be able to access columns with formulas.

Is there a standard way to set the table up so that users can add row after row without coding in the Worksheet Change Event?

Thanks

 

[myall_blues]

You could just unlock the cells in columns 2 and 3 and then protect the sheet, including not allowing users to select locked cells.

 

[HighAndWilder]

You could just unlock the cells in columns 2 and 3 and then protect the sheet, including not allowing users to select locked cells.

View attachment 116854

Thanks. I tried that but the formulas in the locked cells don't work.

I'm developing some code for the Worksheet_Selection Change event but it's quite involved.

 

[myall_blues]

Thanks. I tried that but the formulas in the locked cells don't work.

Locking cells should have no effect on formulas working. I use it regularly to prevent users overwriting formulas. There must be something else wrong.
What do you mean exactly by 'don’t work'?