Jul 26 2023 04:44 PM
Jul 26 2023 04:44 PM
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,
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!
Jul 26 2023 09:05 PM
@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.
Jul 27 2023 06:20 AM
Jul 27 2023 08:22 AM
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:
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!!
Jul 27 2023 08:26 AM
Jul 27 2023 08:47 AMSolution
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
Jul 27 2023 08:54 AM
Alternatively you may create helper spill, e.g. at Sheet2!L3, as
and create drop-down with the reference
It also will be dynamic.
Jul 27 2023 09:43 AM - edited Jul 27 2023 09:48 AM
I'm going to try this and have my daughter test it.
Thank you again!! :)
Jul 28 2023 05:26 AM
Jul 28 2023 07:17 AM
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.
Aug 05 2023 03:35 PM
Aug 05 2023 05:05 PM
Aug 07 2023 04:55 AM
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.