Forum Discussion
Why do dropdown lists appear differently on different computers and can it be changed?
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
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.
Hi!
Thank you!
I'm going to try this and have my daughter test it.
Thank you again!! 🙂
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- Thank you so much, Sergei! I used the OFFSET one and it worked, thank you so much! 🙂
Have a wonderful day!
Diane
