Excel SMART Drop Down List

%3CLINGO-SUB%20id%3D%22lingo-sub-1525032%22%20slang%3D%22en-US%22%3EExcel%20SMART%20Drop%20Down%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1525032%22%20slang%3D%22en-US%22%3E%3CP%3Eis%20there%20anyways%20I%20can%20have%20users%20ADD%20new%20data%20in%20a%20pre%20existing%20drop%20down%20list%3F%20Example-users%20types%20in%20new%20name%20of%20a%20product%20which%20doesn't%20exist%20in%20drop%20down%20list.%20These%20new%20items%20become%20part%20of%20main%20DATA%20file%20(on%20a%20separate%20sheet)%20and%20they%20also%20become%20part%20of%20drop%20down%20list.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1525032%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1525180%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20SMART%20Drop%20Down%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1525180%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F712830%22%20target%3D%22_blank%22%3E%40gdubey%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20on%20Excel%20with%20dynamic%20arrays%2C%20spill%20by%20UNIQUE%20all%20names%20from%20data%20and%20use%20reference%20on%20spill%20in%20data%20validation.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20general%20google%20for%20dynamic%20drop-down%20list%2C%20lot%20of%20samples.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1525231%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20SMART%20Drop%20Down%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1525231%22%20slang%3D%22en-US%22%3EThanks%20for%20your%20input%20but%20I%20have%20no%20idea%20what%20you%20are%20saying%3F%20Can%20you%20provide%20sample%20or%20something%20else%20so%20I%20can%20understand%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1525299%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20SMART%20Drop%20Down%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1525299%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F712830%22%20target%3D%22_blank%22%3E%40gdubey%3C%2FA%3E%26nbsp%3B%2C%20you%20can%20have%20your%20items%20as%20a%20Table%20(Control-T%20to%20create%20the%20table)%20and%20use%20that%20in%20the%20data%20validation%20drop%20down%20list.%20See%20attached...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1525327%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20SMART%20Drop%20Down%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1525327%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F383224%22%20target%3D%22_blank%22%3E%40TheAntony%3C%2FA%3Ealso%20I%20am%20not%20adding%20this%20data%20in%20Items%20table.%20I%20want%20to%20add%20new%20data%20where%20you%20have%20DD%20list%20(bbb).%20I%20am%20assuming%20my%20data%20file%20is%20locked%20for%20users%20so%20they%20can't%20mess%20it%20up.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1525324%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20SMART%20Drop%20Down%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1525324%22%20slang%3D%22en-US%22%3E%3CP%3Enext%20post%20please%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1525416%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20SMART%20Drop%20Down%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1525416%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F712830%22%20target%3D%22_blank%22%3E%40gdubey%3C%2FA%3E%26nbsp%3B%2C%20I'm%20not%20sure%20I%20understand%20completely%20what%20you%20are%20trying%20to%20achieve.%20Can%20you%20provide%20an%20example%20file%20(confidential%20content%20removed)%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1526509%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20SMART%20Drop%20Down%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1526509%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F383224%22%20target%3D%22_blank%22%3E%40TheAntony%3C%2FA%3Esure..lets%20say%26nbsp%3B%20I%20am%20building%20a%20some%20sort%20of%20Billing%20form%20where%20I%20have%20columns%20like%20date%2C%20manager%20name%2C%20account%20numbers%2C%20departments%20etc%20etc.%20All%20the%20data%20for%20this%20billing%20form%20located%20in%20different%20sheet%20and%20I%20am%20using%20vlookup%20formula%20so%20when%20someone%20selects%20the%20department%2C%20departments%20account%20number%20pop%20ups%20in%202nd%20column.%20Here%20is%20my%20problem----%3C%2FP%3E%3CP%3EMy%20data%20is%20going%20to%20change%20all%20the%20time-means%20new%20managers%20will%20be%20added%2C%20new%20account%20numbers%20will%20be%20created%2C%20etc.%20How%20do%20I%20do%20that%3F%20I%20don't%20want%20to%20go%20and%20add%20stuff%20in%20this%20form%20manually%20all%20the%20time.%20Rather%2C%20I%20want%20to%20have%20USERS%20add%20it%20BUT%20not%20in%20my%20master%20DATA%20file%20(sensitive%20data).%20I%20want%20them%20to%20add%20right%20in%20Billing%20form.%20So%2C%20eg.-they%20type%20some%20info%20in%20DD%20list%20for%20department%20and%20if%20the%20dept.%20is%20not%20there%2C%20they%20type%20it%20and%20it%20gets%20added%20to%20master%20data%20and%20to%20this%20DD%20list.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

is there anyways I can have users ADD new data in a pre existing drop down list? Example-users types in new name of a product which doesn't exist in drop down list. These new items become part of main DATA file (on a separate sheet) and they also become part of drop down list.

7 Replies
Highlighted

@gdubey 

If you on Excel with dynamic arrays, spill by UNIQUE all names from data and use reference on spill in data validation.

 

In general google for dynamic drop-down list, lot of samples.

Highlighted
Thanks for your input but I have no idea what you are saying? Can you provide sample or something else so I can understand?
Highlighted

@gdubey , you can have your items as a Table (Control-T to create the table) and use that in the data validation drop down list. See attached...

Highlighted

next post please

Highlighted

@TheAntonyalso I am not adding this data in Items table. I want to add new data where you have DD list (bbb). I am assuming my data file is locked for users so they can't mess it up.

Highlighted

@gdubey , I'm not sure I understand completely what you are trying to achieve. Can you provide an example file (confidential content removed)?

Highlighted

@TheAntonysure..lets say  I am building a some sort of Billing form where I have columns like date, manager name, account numbers, departments etc etc. All the data for this billing form located in different sheet and I am using vlookup formula so when someone selects the department, departments account number pop ups in 2nd column. Here is my problem----

My data is going to change all the time-means new managers will be added, new account numbers will be created, etc. How do I do that? I don't want to go and add stuff in this form manually all the time. Rather, I want to have USERS add it BUT not in my master DATA file (sensitive data). I want them to add right in Billing form. So, eg.-they type some info in DD list for department and if the dept. is not there, they type it and it gets added to master data and to this DD list.