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?
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?
Can you ask users before typing pressing the TAB key so that the row is inserted before they type?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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 +
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 +
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.
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.
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??
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??
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.
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.
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
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.
ASKER
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.
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.