Dropdown list improvement - data validation lists are dynamic for tables on other sheets

%3CLINGO-SUB%20id%3D%22lingo-sub-1977541%22%20slang%3D%22en-US%22%3EDropdown%20list%20improvement%20-%20data%20validation%20lists%20are%20dynamic%20for%20tables%20on%20other%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1977541%22%20slang%3D%22en-US%22%3E%3CP%3EWe've%20recently%20improved%20the%20way%20that%20data%20validation%20works%20when%20the%20values%20come%20from%20a%20table%20on%20another%20sheet.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt's%20really%20handy%20to%20have%20a%20drop%20down%20list%20for%20a%20cell%20so%20only%20certain%20values%20can%20be%20entered.%20The%20list%20of%20acceptable%20values%20can%20come%20from%20a%20range%20of%20cells%2C%20such%20as%20a%20column%20in%20a%20table.%26nbsp%3B%20It's%20nice%20to%20put%20the%20table%20with%20the%20list%20of%20acceptable%20values%20on%20a%20separate%20sheet%2C%20so%20it's%20out%20of%20the%20way.%20If%20you%20add%20a%20new%20value%20to%20the%20table%20and%20it's%20on%20the%20same%20sheet%20as%20the%20cell%20with%20the%20dropdown%2C%20then%20the%20new%20value%20would%20show%20up%20in%20the%20dropdown.%20Many%20people%20like%20to%20keep%20the%20table%20on%20a%20different%20sheet%2C%20but%20in%20the%20past%2C%20new%20values%20wouldn't%20automatically%20show%20up%20in%20the%20dropdown%20list%20in%20that%20case.%20%3CSTRONG%3ENow%2C%20if%20you%20add%20new%20values%20to%20the%20table%2C%20they'll%20show%20up%20in%20the%20dropdown%2C%20even%20if%20the%20table%20is%20on%20another%20sheet.%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22DV%20list2.gif%22%20style%3D%22width%3A%20853px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F239674i4B0FC5E982B2AED4%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22DV%20list2.gif%22%20alt%3D%22Animated%20screen%20shot%20showing%20a%20new%20value%20being%20added%20to%20a%20table%20then%20showing%20up%20in%20a%20data%20validation%20dropdown%20list%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EAnimated%20screen%20shot%20showing%20a%20new%20value%20being%20added%20to%20a%20table%20then%20showing%20up%20in%20a%20data%20validation%20dropdown%20list%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EThere%20were%20some%20ways%20to%20work%20around%20this%2C%20but%20now%20it%20just%20works.%26nbsp%3B%26nbsp%3BThis%20was%20requested%20on%20%3CA%20title%3D%22Data%20validation%20list%20sources%20referring%20to%20tables%20are%20only%20dynamic%20for%20tables%20on%20the%20same%20sheet%22%20href%3D%22https%3A%2F%2Fexcel.uservoice.com%2Fforums%2F304921%2Fsuggestions%2F11555598%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EExcel.Uservoice.com%3C%2FA%3E%2C%20and%20we%20are%20happy%20to%20announce%20that%20we've%20completed%20it.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDetails%3A%20This%20improvement%20is%26nbsp%3B%3CSPAN%3Eavailable%20in%20version%2016.0.133%20or%20greater%20on%20Windows%2C%20and%2016.42%20or%20greater%20on%20Mac.%20For%20more%20information%20about%20creating%20dropdown%20lists%2C%20see%20our%20help%20topic%20-%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Foffice%2Fcreate-a-drop-down-list-7693307a-59ef-400a-b769-c5402dce407b%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ECreate%20a%20drop-down%20list%20-%20Office%20Support%20(microsoft.com)%3C%2FA%3E.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1977541%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Microsoft

We've recently improved the way that data validation works when the values come from a table on another sheet. 

 

It's really handy to have a drop down list for a cell so only certain values can be entered. The list of acceptable values can come from a range of cells, such as a column in a table.  It's nice to put the table with the list of acceptable values on a separate sheet, so it's out of the way. If you add a new value to the table and it's on the same sheet as the cell with the dropdown, then the new value would show up in the dropdown. Many people like to keep the table on a different sheet, but in the past, new values wouldn't automatically show up in the dropdown list in that case. Now, if you add new values to the table, they'll show up in the dropdown, even if the table is on another sheet.

 

Animated screen shot showing a new value being added to a table then showing up in a data validation dropdown listAnimated screen shot showing a new value being added to a table then showing up in a data validation dropdown list

There were some ways to work around this, but now it just works.  This was requested on Excel.Uservoice.com, and we are happy to announce that we've completed it. 

 

Details: This improvement is available in version 16.0.133 or greater on Windows, and 16.42 or greater on Mac. For more information about creating dropdown lists, see our help topic - Create a drop-down list - Office Support (microsoft.com).

0 Replies