Hi Everyone, I have a question relating to data validation formulas:
I have two columns that I am using for defining details for products. The first column is called Category. The data validation for this is done from a simple named range called "Category" and is working properly. The second column is called SubCategory. The data validation for this is done with an array formula: =OFFSET(SubCategory,MATCH($E2,Category,0)-1,0,COUNTIF(Category,$E2)) In the worksheet I am using to define products the Category value is in column E The master data for the Category-SubCategory possible value combinations looks like this: Category SubCategory Segment A Red 1 A Green 2 A Green 3 A Blue 4 A Blue 5 B Red 6 B Red 7 B Purple 8 B Purple 9 B Orange 10 C Cyan 11 C Cyan 12 C White 13 C Green 14 C Green 15 When I use the above formula to generate a list of SubCategories for B for example I will have Red x 2, Purple x 2, and Orange x 1. What I would like to have 1 simply Red, Purple, Orange (once each) Etc..... So the idea is that my OFFSET formula is pulling SubCategory values matched on Category values - and it is doing this as expected. How do I limit the data validation list to just unique values? Any help greatly appreciated. Cheers -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at https://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
