Why do dropdown lists appear differently on different computers and can it be changed?

[DianeDennis]

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!

[#2]

@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.

[#3]

It sounds like the source for the data validation list is another workbook.

[#4]

@Riny_van_Eekelen 

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$500

Every 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!!

[#5]

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!

[#6 · Accepted solution]

@DianeDennis 

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

[#7]

@DianeDennis 

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.

[#8]

@Sergei Baklan 

Hi!

Thank you!

I'm going to try this and have my daughter test it.

Thank you again!! :)

[#9]

Thank you so much, Sergei! I used the OFFSET one and it worked, thank you so much! :)

Have a wonderful day!
Diane

[#10]

@DianeDennis 

Hi Diane, glad it works, thank you for the feedback. However, on 365 second option with TOCOL() or FILTER() is more preferrable from performance point of view. OFFSET() shall work for any Excel.

Have a nice day.

[#11]

Hi Sergei!

Thank you so much!

I just saw this message from you, I apologize for my delay in responding.

My goal is to have it work for as many versions of Excel as possible even though I'm using 365 to create it.

That being said, is there anything I should be concerned about for folks using 365 if I use OFFSET instead of TOCOL or FILTER? You mentioned a better performance from TOCOL and/or FILTER for 365, does that mean that OFFSET might occasionally not work properly in 365?

Thank you again so much! Have a great weekend!
Diane

[#12]

I guess all works in 365.
But if many items available for pick,the Offset function will lead to run some slowly or response lag compared with FILTER in 365.
If too many items ,you many need database for quick serach.

[#13]

@DianeDennis 

OFFSET() itself is quite fast function and it works on all versions of Excel. The problem is they are  recalculated each time you do any calculation in the workbook. In simplest case, entering into any cell =1+2 forces recalculation of all OFFSET().

Thus all depends on our file, is that critical or not.