Excel Table - Adding Rows when First Column has Data Validation

Excel Table Support.
I have created a Table in an excel worksheet where users will be expected to add new rows manually. Note that this document will be a shared document on Sharepoint - so I cannot use any VBA etc.

I have created a number of other tables in the workbook that act as Data Validation lists. The data validation etc is working fine.

However - this is my problem. The user only has to enter two pieces of information in the table - and both pieces of data are entered into the first two cells/columns of the table. Normally this works perfectly - you just go to the first row under the table and begin typing - and when you tab to the next cell - the table automatically expands to add the row etc.
However, the first column (and second column) are both controlled by a Data Validation list.

The issue is - if you click on the cell below the table - the data validation is not available yet (as the table has not expanded) - so the person cannot "Select" from the list. If they attempt to type something - if they get it wrong and tab to the next column then they will of course get an error - because the table will expand and the cell now contains validation - so they either have to retry or cancel - both of which "remove" the new table row added - so the validation goes away - so they cannot select from the drop list (as it no longer exists on the cell).
So I am kind of between a rock and a hard place. I want to make it as simple as possible - basically being able to select from the right values in a new row - but they are not going to do the whole "click on the bottom row of the table, right click and select insert, then select "Insert table row below" - thats just not going to happen.

Anyone have any bright ideas on how I can allow selection or table row creation "easily" when you have data validation in the first column?
Steve MonkDirectorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Professor JMicrosoft Excel ExpertCommented:
Can you ask users before typing pressing the TAB key so that the row is inserted before they type?
Steve MonkDirectorAuthor Commented:
Hi - I happened to find my own solution. It was actually relatively simple.
All I need to do is put the same data validation that is in the first column of the Table - and duplicate it in the rows below the table (i.e. down the column outside the table).
I can do this for a couple of thousand rows - so it can cover what records will be added. The user can then select a value (as the drop list will work) - and as soon as they select the value - the table tow is added - and the table expands and the data validation is still present.

Problem solved.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Steve MonkDirectorAuthor Commented:
ProfessorJimJam - unfortunately Tab will not work - using tab in the first row below the table does not seem to do anything (i.e. it does not expand the table). So thank you for the suggestion - but it does not work., though it was a helpful suggestion to try.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Professor JMicrosoft Excel ExpertCommented:
Steve,

Glad you found your solution by yourself.    Yes, the Tab works only if the active cell is the last cell on the table.  but another keyboard shortcut to insert a new row,  is Control and Shift and +
Rob HensonFinance AnalystCommented:
Tab from the last table cell to create a new row is a Word feature not Excel.

Pre-populating the cells below the table will include them in the Used Range of the sheet and will as a consequence increase the file size. For a couple of thousand rows shouldn't be too much of an issue but it depends on how wide the table is; might only be 2000 rows but if your table is 6 columns wide, that's 12000 cells that excel is storing.

If you look at the bottom right of the table, the last cell will have a triangle filling the bottom right corner; you can use this to drag the table down before entering in first column. The user could do this as a one off for a dozen or more rows at the start of entering some data and then drag it back up when finished to take table back up to the last entry.
Rob HensonFinance AnalystCommented:
Another option, would be to use the right click menu in the cell below the table. There is an option in the right click menu to "Pick from drop-down list". This shows a list of all previous entries from the column above, if all of these are from the Data Validation then the pick list will effectively replicate the Data Validation, at least for those items that have been used previously.

To get all items from the Data Validation, you could copy/move the Validation list above the header row of the table with no blank rows between the last entry in the list and the header. Then hide the validation list rows. The right click will then show all of the Data Validation options. It will also include the header but I assume your users would recognise not to use that??
Rob HensonFinance AnalystCommented:
Thought of another option, include a cell containing just a nominal value eg "aaa" in the Data Validation list. The user can then just enter that value in the cell below the table and press enter. It will be recognised as valid when the table expands; the user can then use the drop-down to select a proper value. I have deliberately suggested "aaa" so that when the Validation selection list is selected the entry already populated will be selected and if that is at the top of the list the user can then scroll down rather than having to think about having to scroll up or down for the entry they require.

Keyboard shortcut which might help users is Alt+Down Arrow.

If Data Validation is in place it will activate the Validation drop-down, if not it will activate the right click, select from List option.
Professor JMicrosoft Excel ExpertCommented:
J.gif
Rob,   Your comment
Tab from the last table cell to create a new row is a Word feature not Excel.


It works in Excel.  please see attached file and the animated gif.
Book1.xlsm
Rob HensonFinance AnalystCommented:
Apologies, Tab didn't do that for me in the file I created to test the options. I was using Excel 2016; I did have another big file open as well that was causing some calculation delays so maybe that stopped it.
Steve MonkDirectorAuthor Commented:
Thanks all for those who commented and provided suggestions - the items you provided were certainly helpful.
Fortunately my file is "relatively" small - so adding rows under the table with the same validation I think will do it for now. The table only has 6 columns.
I could not use the suggestion to add all the possible values in hidden rows at the start of the table as the validation list is actually a dynamic list that grows (user firsts enters values on a different worksheet that automatically is added to the validation list - then they have to select the value in the other worksheet). Also the result will be used in at pivot table - so could not include hidden rows even if that were feasible.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ACT

From novice to tech pro — start learning today.