Positioning active cell when adding table rows.

HighAndWilder

Well-known Member
Joined
Nov 4, 2006
Messages
618
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
You could just unlock the cells in columns 2 and 3 and then protect the sheet, including not allowing users to select locked cells.

1726280868660.png
 
Upvote 0
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'?
 
Upvote 0

Forum statistics

Threads
1,223,374
Messages
6,171,718
Members
452,418
Latest member
kennettz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top