Forum Discussion
Solved
Why do dropdown lists appear differently on different computers and can it be changed?
Hi!
I've created a dropdown list and the table that it references has 500 rows but only the first 20 rows are filled.
On my computer, running Excel 365 and Windows 10, when I click on the dropdown list a little window pops open with a scroll bar and I see the list of names from the column in the table that it references starting with the first name plus one blank row at the bottom.
On my daughter's computer, running Excel 365 and Windows 10, when she clicks on the dropdown list the window that pops open is blank (with a scroll bar). Instead of showing the name choices, the dropdown list is presenting the first unpopulated cell/row from that table and then a whole bunch more blanks.
Initially we thought the names were missing from the dropdown list but then we realized that we could scroll up in the list to see the names.
Is there a way to:
A) force the dropdown list to present the list of names on everyone's computer the same way it appears on mine, starting from the top of the list of names, instead of starting with the unpopulated rows/cells and forcing the user to scroll up,
and
B) have it show only one blank row at the bottom of the dropdown list instead of all the blank rows in the table it references?
Thank you so much!
If data validation list is based on the structured table located in the same sheet of the workbook, it expanded automatically with expanding of the table.
If table is on another sheet of same workbook, that doesn't work. As workaround for the drop-down list you may use formula like
=OFFSET(Sheet2!$A$2,0,0, COUNTA(Sheet2!$A$2:$A$500) )instead of the direct reference
=Sheet2!$A$2:$A$500
- It sounds like the source for the data validation list is another workbook.
- Hi! Thank you! 🙂
Both worksheets are in the same workbook.
I laid out further what my workbook is like in my response to Riny_van_Eekelen and what I'm trying to accomplish. Thank you so much! Any ideas/suggestions/etc. that you have are greatly appreciated!
DianeDennis I suspect that your daughter is running an older version of Excel365. Can't remember when exactly the behaviour you describe (skipping blanks and duplicates) for your set-up was introduced, but I believe it was less than a year ago.
How to prevent blanks from showing in the drop down list? You mention that the list is linked to a table with 500 rows of which only 20 are filled. Resize the table to only 20 rows and make sure it is formatted as an 'Excel table'. The drop down list will automatically expand/contract when you add/remove rows to/from the table.
Hi! Thank you. 🙂 She said she was updated, I'll ask her to check again. 🙂
Thank you also for your suggested solution. 🙂
I tried it and realized that something I didn't mention in my original post might make a difference:
Both the worksheet with the tables and the worksheet that references the tables are protected. Not with a password but the preference is for the end user to not have to unprotect the worksheets to add additional rows and update the source code for the data validation.
I did what you said and resized the table (it's now 7 rows plus header).
I then protected the worksheet with the table and the worksheet that references the table.
I then tried to add a row to the table on the first worksheet and it didn't work. I tabbed through the row and it tabbed back to the beginning and down to the next row but the row didn't adopt the table design, nor were the other cells in the row formatted as they should have been (percentages, dollars, etc.).
When I went back to the second worksheet (also protected) where the table from the first worksheet is referenced, the test name that I added wasn't available in the dropdown list/the source code in the data validation hadn't updated to include the added row.
I then unprotected the first worksheet (with the table).
When I added a row to the table it worked. A new row added automatically when I tabbed through the table and it adopted the characteristics/formatting of the table (as did the individual cells). But on the second worksheet the test name wasn't available in the dropdown list/the source code in the data validation didn't update to include the new row.
That's why I had it set up like this:
500 rows in the table with the data validation source code being:
=Tables1!$A$2:$A$500Every time the end user fills in a blank row the new name is automatically available in the dropdown list. They don't have to unprotect the sheet and they don't have to change the source code (at least until they get to 500). 😉
Is there a way to set it up so that:
A) the end user can add a row to the table (Table name is EE_DB) without having to unprotect the worksheet and
B) the "source" in the data validation for the dropdown list on the other worksheet updates automatically (so that the end user doesn't have to unprotect it and change the source code themselves)?
C) have the blank rows not show in the dropdown list or if they have to show then force the dropdown list to display from the first row (which would be row 2 of the table because there's a header)?
Thank you again so much!!
Alternatively you may create helper spill, e.g. at Sheet2!L3, as
=TOCOL(Sheet2!A2:A500,3)and create drop-down with the reference
=Sheet2!$L$3#It also will be dynamic.
