AndyPandy27
Board Regular
- Joined
- Jul 17, 2012
- Messages
- 142
Hi All,
I struggled a bit with the title - so I'll try to explain more clearly.
I have created a Table in Excel, which I intend to share with a selection of users to populate. This will result in a indeterminate number of rows being populated.
Each column in my Table (10 columns in total) has Data Validation included - several of which are Data Validation Lists based on some named ranges I have in a separate worksheet (same workbook).
Once set-up, I see everything as expected: a Header Row and the first, blank data row are visible - excellent.
All Data Validation is working perfectly for that first row - with List dropdown arrows appearing when someone enters a cell - excellent.
My issue is that once that first row has been populated, a new Table row does not become activated until someone starts entering data into the first row below the Table. This means that none of the dropdowns appear to provide users with the acceptable values - not so excellent.
If a user were to manually enter an acceptable value in a cell, the Table expands and the row becomes activated and all Data Validation fills down accurately with no issues. But for a large number of users, this is not a viable solution. There will definitely be a large number of people who will struggle, and so it's necessary that for each row they have the dropdown available to view the full list.
Is there a way to force Excel to always add a Table row when new data is entered, so the Data Validation List dropdown arrows will always be available?
Alternatively, is there a better solution I could employ to achieve the same goal: get users to enter accurate data by restricting the values they can enter where will do not know the required number of rows. This part is me saying, I could go ahead and create a standard spreadsheet (no Table) and prepopulate X number of rows with Data Validation and distribute that to my users (over-estimating the number of rows needed, in an effort to avoid it getting full). But I was hoping for something more sophisticated...
NB - it would be ideal to implement a non VBA solution if possible, as distributing macro-enabled files is very much subject to things going wrong. We cannot go through the certification thing to ensure a document is considered "safe" allow me to force macros to be enabled for other users. As such we rely on users making sure to have clicked the "Enable Content" button that appears and... well, you can guess how successful that often is. If VBA is the best/only option, I can live with that and implement the workarounds available to help make people click that button - but if there's a simpler solution, so much the better.
Thank you.
I struggled a bit with the title - so I'll try to explain more clearly.
I have created a Table in Excel, which I intend to share with a selection of users to populate. This will result in a indeterminate number of rows being populated.
Each column in my Table (10 columns in total) has Data Validation included - several of which are Data Validation Lists based on some named ranges I have in a separate worksheet (same workbook).
Once set-up, I see everything as expected: a Header Row and the first, blank data row are visible - excellent.
All Data Validation is working perfectly for that first row - with List dropdown arrows appearing when someone enters a cell - excellent.
My issue is that once that first row has been populated, a new Table row does not become activated until someone starts entering data into the first row below the Table. This means that none of the dropdowns appear to provide users with the acceptable values - not so excellent.
If a user were to manually enter an acceptable value in a cell, the Table expands and the row becomes activated and all Data Validation fills down accurately with no issues. But for a large number of users, this is not a viable solution. There will definitely be a large number of people who will struggle, and so it's necessary that for each row they have the dropdown available to view the full list.
Is there a way to force Excel to always add a Table row when new data is entered, so the Data Validation List dropdown arrows will always be available?
Alternatively, is there a better solution I could employ to achieve the same goal: get users to enter accurate data by restricting the values they can enter where will do not know the required number of rows. This part is me saying, I could go ahead and create a standard spreadsheet (no Table) and prepopulate X number of rows with Data Validation and distribute that to my users (over-estimating the number of rows needed, in an effort to avoid it getting full). But I was hoping for something more sophisticated...
NB - it would be ideal to implement a non VBA solution if possible, as distributing macro-enabled files is very much subject to things going wrong. We cannot go through the certification thing to ensure a document is considered "safe" allow me to force macros to be enabled for other users. As such we rely on users making sure to have clicked the "Enable Content" button that appears and... well, you can guess how successful that often is. If VBA is the best/only option, I can live with that and implement the workarounds available to help make people click that button - but if there's a simpler solution, so much the better.
Thank you.
