@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.
