SOLVED

More Drop Down Menu Stuff

%3CLINGO-SUB%20id%3D%22lingo-sub-2362087%22%20slang%3D%22en-US%22%3EMore%20Drop%20Down%20Menu%20Stuff%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2362087%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20I%20was%20wondering%20if%20someone%20could%20point%20me%20in%20the%20right%20direction.%20I%20need%20the%20category%20Drop%20down%20menu%20to%20change%20to%20the%20appropriate%20salvage%20values%20for%20that%20said%20category.%20Right%20now%20i%20Just%20have%20the%20Furniture%20Salvage%20value%20showing%20from%20a%20code%20I%20used%20from%20%3CA%20href%3D%22https%3A%2F%2Fwww.extendoffice.com%2Fdocuments%2Fexcel%2F4130-excel-drop-down-list-show-different-value.html%23a1)But%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.extendoffice.com%2Fdocuments%2Fexcel%2F4130-excel-drop-down-list-show-different-value.html%23a1%3C%2FA%3E%20I%20only%20want%20that%20salvage%20value%20to%20come%20up%20for%20the%20right%20category%20on%20row%205.%3C%2FP%3E%3CP%3EAny%20Help%20Will%20be%20awesome%2C%20thanks!%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2362087%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2362403%22%20slang%3D%22en-US%22%3ERe%3A%20More%20Drop%20Down%20Menu%20Stuff%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2362403%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1045193%22%20target%3D%22_blank%22%3E%40N7Skyfire%3C%2FA%3E%26nbsp%3B%20it%20appears%20there%20are%20a%20few%20problems%20with%20the%20sheet%2Fmacro.%26nbsp%3B%20basicallly%20you%20are%20referring%20to%20a%20fixed%20table%20in%20the%20macro%20and%20don't%20switch%20it%20based%20on%20the%20selection%2C%20but%20there%20are%20other%20issues.%26nbsp%3B%20Besides%2C%20I%20don't%20even%20know%20why%20you%20want%20to%20replace%20the%20name%20of%20the%20item%20you%20are%20selecting%20with%20the%20value%20because%20then%20you%20have%20no%20reference%20to%20what%20you%20actually%20selected.%20I%20am%20attaching%20a%20workbook%20with%20a%20new%20sheet%20where%20I%20re-did%20it%20in%20what%20I%20consider%20a%20better%20format%3A%3C%2FP%3E%3CP%3Ea)%20i%20added%20a%20row%2Fcell%20with%20the%20item%20name%20that%20is%20selected%20using%20the%20drop%20down%20and%20then%20the%20item%20value%20is%20found%20using%20a%20lookup%3C%2FP%3E%3CP%3E%26nbsp%3B%20-%20as%20I%20noted%20I%20like%20this%20better%20because%20then%20you%20can%20always%20see%20what%20you%20selected.%3C%2FP%3E%3CP%3Eb)%20the%20table%20now%20includes%20all%20the%20items%20and%20uses%20the%20'Category'%20column%20to%20group%20them%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B-%20this%20is%20better%20because%20you%20can%20sort%2C%20manipulate%2C%20view%20them%20all%2C%20and%20add%20new%20items%20easier%20(imho)%3C%2FP%3E%3CP%3Ec)%20the%20category%20drop%20down%20and%20the%20item%20drop%20down%20are%20dynamic%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B-%20so%20now%20if%20you%20add%20a%20new%20item%20under%20a%20category%20'Jo-Jo%20Stuff'%20that%20new%20category%20will%20show%20up%20as%20an%20option%20in%20the%20drop%20down%3C%2FP%3E%3CP%3Ed)%20since%20this%20is%20all%20formula%20based%20you%20can%2Fshould%20delete%20the%20original%20sheets%20and%20save%20this%20as%20an%20xlsx%20(non-macro)%20based%20sheet%20and%20you%20won't%20have%20to%20worry%20about%20security%20settings%20any%20more.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewhat%20I%20did%20not%20do%20is%20account%20for%20the%20possibility%20of%20the%20same%20item%20name%20under%20multiple%20categories%2C%20hope%20that%20isn't%20a%20problem%2C%20but%20if%20it%20is%20we%20could%20change%20the%20lookup%20to%20a%20multiple%20factor%20filter.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eyou%20can%20also%20mover%20the%20master%20table%20and%20dynamic%20drop%20down%20list%20off%20the%20screen%20or%20hide%20those%20columns.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Esee%20attached%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2362468%22%20slang%3D%22en-US%22%3ERe%3A%20More%20Drop%20Down%20Menu%20Stuff%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2362468%22%20slang%3D%22en-US%22%3EThat%20works%20for%20me%2C%20thank%20you%20very%20much%20for%20taking%20the%20time%20to%20do%20that.%20You%20have%20any%20links%20for%20what%20you%20did%20or%20that%20just%20stuff%20you%20remembered%3F%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello, I was wondering if someone could point me in the right direction. I need the category Drop down menu to change to the appropriate salvage values for that said category. Right now i Just have the Furniture Salvage value showing from a code I used from https://www.extendoffice.com/documents/excel/4130-excel-drop-down-list-show-different-value.html#a1 I only want that salvage value to come up for the right category on row 5.

Any Help Will be awesome, thanks!

4 Replies
best response confirmed by N7Skyfire (Occasional Contributor)
Solution

@N7Skyfire  it appears there are a few problems with the sheet/macro.  basicallly you are referring to a fixed table in the macro and don't switch it based on the selection, but there are other issues.  Besides, I don't even know why you want to replace the name of the item you are selecting with the value because then you have no reference to what you actually selected. I am attaching a workbook with a new sheet where I re-did it in what I consider a better format:

a) i added a row/cell with the item name that is selected using the drop down and then the item value is found using a lookup

  - as I noted I like this better because then you can always see what you selected.

b) the table now includes all the items and uses the 'Category' column to group them

   - this is better because you can sort, manipulate, view them all, and add new items easier (imho)

c) the category drop down and the item drop down are dynamic 

   - so now if you add a new item under a category 'Jo-Jo Stuff' that new category will show up as an option in the drop down

d) since this is all formula based you can/should delete the original sheets and save this as an xlsx (non-macro) based sheet and you won't have to worry about security settings any more.

 

what I did not do is account for the possibility of the same item name under multiple categories, hope that isn't a problem, but if it is we could change the lookup to a multiple factor filter.

 

you can also mover the master table and dynamic drop down list off the screen or hide those columns.

 

see attached file.

 

That works for me, thank you very much for taking the time to do that. You have any links for what you did or that just stuff you remembered?
there are lots of tutorials on how to create filtered drop down lists. Here is one:
https://www.xelplus.com/searchable-drop-down-list/
Thanks again for the help! and For the link!