Home

Adding Items in Table for Data Validation Menu

%3CLINGO-SUB%20id%3D%22lingo-sub-359165%22%20slang%3D%22en-US%22%3EAdding%20Items%20in%20Table%20for%20Data%20Validation%20Menu%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-359165%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20couple%20tables%20that%20I'm%20using%20as%20the%20source%20for%20some%20Data%20Validation%20drop%20down%20menus.%20If%20I%20want%20to%20add%20items%20to%20these%20tables%20and%20have%20them%20automatically%20update%20in%20the%20drop%20down%20menus%2C%20I'm%20having%20to%20insert%20the%20items%20somewhere%20below%20the%20first%20cell%20and%20above%20the%20last%20cell.%20If%20I%20try%20to%20insert%20an%20item%20above%20the%20first%20item%20or%20below%20the%20last%20item%20(even%20though%20it's%20still%20in%20the%20%22table%22%20and%20the%20table%20automatically%20expands)%2C%20those%20items%20don't%20update%20in%20my%20drop%20down%20menu.%20Why%20is%20this%3F%20Is%20there%20something%20I%20can%20do%20to%20make%20this%20work%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-359165%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-359933%22%20slang%3D%22en-US%22%3ERe%3A%20Adding%20Items%20in%20Table%20for%20Data%20Validation%20Menu%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-359933%22%20slang%3D%22en-US%22%3EHello%20Adam%2C%3CBR%20%2F%3EAssuming%20your%20items%20are%20Item1%20to%20Item4%20and%20are%20listed%20in%20A1%3AA4%2C%20name%20your%20list%20as%20%E2%80%9CItems%E2%80%9D%20with%20this%20formula%3A%3CBR%20%2F%3E%3DINDEX(%24A%3A%24A%2C1)%3AINDEX(%24A%3A%24A%2CCOUNTA(%24A%3A%24A))%3CBR%20%2F%3EIn%20the%20cell%20where%20you%20want%20to%20place%20your%20drop-down%20list%2C%20your%20source%20is%3A%3CBR%20%2F%3E%3DItems%3CBR%20%2F%3EThereafter%2C%20any%20item%20that%20you%20add%20before%20Item1%2C%20after%20Item4%2C%20or%20anywhere%20between%20them%2C%20shall%20be%20automatically%20included%20in%20your%20drop-down%20list.%3C%2FLINGO-BODY%3E
Adam Cronce
Occasional Contributor

I have a couple tables that I'm using as the source for some Data Validation drop down menus. If I want to add items to these tables and have them automatically update in the drop down menus, I'm having to insert the items somewhere below the first cell and above the last cell. If I try to insert an item above the first item or below the last item (even though it's still in the "table" and the table automatically expands), those items don't update in my drop down menu. Why is this? Is there something I can do to make this work?

1 Reply
Hello Adam,
Assuming your items are Item1 to Item4 and are listed in A1:A4, name your list as “Items” with this formula:
=INDEX($A:$A,1):INDEX($A:$A,COUNTA($A:$A))
In the cell where you want to place your drop-down list, your source is:
=Items
Thereafter, any item that you add before Item1, after Item4, or anywhere between them, shall be automatically included in your drop-down list.
Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies