SOLVED
Home

Drop Down List Based on Previous Column's Drop Down List Selection - Categories/Sub-Categories

%3CLINGO-SUB%20id%3D%22lingo-sub-353411%22%20slang%3D%22en-US%22%3EDrop%20Down%20List%20Based%20on%20Previous%20Column's%20Drop%20Down%20List%20Selection%20-%20Categories%2FSub-Categories%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-353411%22%20slang%3D%22en-US%22%3E%3CP%3EAny%20help%20is%20appreciated%20on%20the%20following.%20%26nbsp%3BThanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20setting%20up%20a%20sheet%20with%20columns%20to%20categorize%2Fsub-categorize%20data.%3C%2FP%3E%3CP%3EI'd%20like%20a%20Category%20Column%20which%20has%20a%20Drop-Down%20List%20and%20a%20Sub-Category%20Column%20with%20a%20Drop-Down%20List%20based%20on%20the%20selected%20%22Category%22%20from%20the%20previous%20Drop-Down%20List.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBasically%2C%20if%20my%20lists%20look%20as%20follows%20below%20(Category%20List%2FSub-Category%20List)%2C%20how%20do%20I%20display%20the%20Sub-Category%20Drop-Down%20List%20for%20the%20corresponding%20Category%20(%3CEM%3Eonly%3C%2FEM%3E)%20without%20having%20to%20include%20*all*%20Sub-Categories%20to%20choose%20from%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EIf%20%22Category%20Column%22%20Drop-Down%20Selection%20%3D%20Policy%2C%20then%3CUL%3E%3CLI%3E%22Sub-Category%20Column%22%20Drop-Down%20Selection%20%3D%20only%20the%20option%20of....%3CUL%3E%3CLI%3E%22Personnel%2C%20Product%2C%20Refund%2FCancellation%2C%20Safety%2C%20Security%2C%20Payroll%2C%20Attendance%22%3C%2FLI%3E%3C%2FUL%3E%3C%2FLI%3E%3C%2FUL%3E%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ECategories%20Drop-Down%20List%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CUL%3E%3CLI%3E%3CSTRONG%3EPromotions%20Category%3C%2FSTRONG%3E%3CUL%3E%3CLI%3E%3CSTRONG%3ESub-Categories%20for%20Promotions%20Category%3C%2FSTRONG%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CUL%3E%3CLI%3E%3CSPAN%3ECorporate%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EMembership%20Based%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3ECustomer%20Type%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3ESale%3C%2FSPAN%3E%3C%2FLI%3E%3C%2FUL%3E%3C%2FLI%3E%3C%2FUL%3E%3C%2FLI%3E%3CLI%3E%3CSTRONG%3E%3CSPAN%3EPolicy%20Category%3C%2FSPAN%3E%3C%2FSTRONG%3E%3CUL%3E%3CLI%3E%3CSTRONG%3E%3CSPAN%3ESub-Categories%20for%20Policy%20Category%3A%3C%2FSPAN%3E%3C%2FSTRONG%3E%3CUL%3E%3CLI%3E%3CSPAN%3EPersonnel%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EProduct%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3ERefund%2FCancellation%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3ESafety%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3ESecurity%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EPayroll%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EAttendance%3C%2FSPAN%3E%3C%2FLI%3E%3C%2FUL%3E%3C%2FLI%3E%3C%2FUL%3E%3C%2FLI%3E%3CLI%3E%3CSTRONG%3E%3CSPAN%3ESystem%20Category%3C%2FSPAN%3E%3C%2FSTRONG%3E%3CUL%3E%3CLI%3E%3CSTRONG%3E%3CSPAN%3ESub-Cateogories%20for%20System%20Category%3A%3C%2FSPAN%3E%3C%2FSTRONG%3E%3CUL%3E%3CLI%3E%3CSPAN%3EUpdate%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EEnhancement%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EReplacement%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EAlert%3C%2FSPAN%3E%3C%2FLI%3E%3C%2FUL%3E%3C%2FLI%3E%3C%2FUL%3E%3C%2FLI%3E%3CLI%3E%3CSTRONG%3E%3CSPAN%3EProcess%20Category%3C%2FSPAN%3E%3C%2FSTRONG%3E%3CUL%3E%3CLI%3E%3CSTRONG%3E%3CSPAN%3ESub-Categories%20for%20Process%20Category%3A%3C%2FSPAN%3E%3C%2FSTRONG%3E%3CUL%3E%3CLI%3E%3CSPAN%3ECall%20Handling%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EProduct%20Sales%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3ERequests%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EComplaints%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EIssuing%20Compensation%3C%2FSPAN%3E%3C%2FLI%3E%3C%2FUL%3E%3C%2FLI%3E%3C%2FUL%3E%3C%2FLI%3E%3C%2FUL%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-353411%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-391292%22%20slang%3D%22en-US%22%3ERe%3A%20Drop%20Down%20List%20Based%20on%20Previous%20Column's%20Drop%20Down%20List%20Selection%20-%20Categories%2FSub-Categories%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-391292%22%20slang%3D%22en-US%22%3EWelcome%2C%20Donna.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-391290%22%20slang%3D%22en-US%22%3ERe%3A%20Drop%20Down%20List%20Based%20on%20Previous%20Column's%20Drop%20Down%20List%20Selection%20-%20Categories%2FSub-Categories%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-391290%22%20slang%3D%22en-US%22%3EThanks%20so%20much%20for%20the%20helpful%20direction%2C%20Twifoo!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-355216%22%20slang%3D%22en-US%22%3ERe%3A%20Drop%20Down%20List%20Based%20on%20Previous%20Column's%20Drop%20Down%20List%20Selection%20-%20Categories%2FSub-Categories%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-355216%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Donna%2C%26nbsp%3B%3C%2FP%3E%3CP%3EExcept%20that%20there%20are%20only%202%20drop-down%20lists%20herein%2C%20the%20attached%20file%20is%20exactly%20the%20same%20as%20that%20which%20I%20uploaded%20in%20this%20link%2C%20which%20has%203%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2FDependent-Drop-down-List-Formula-for-only-showing-cells-with%2Fm-p%2F355193%23M26086%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2FDependent-Drop-down-List-Formula-for-only-showing-cells-with%2Fm-p%2F355193%23M26086%3C%2FA%3E%3C%2FP%3E%3CP%3ECheers%2C%3C%2FP%3E%3CP%3ETwifoo%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-353548%22%20slang%3D%22en-US%22%3ERe%3A%20Drop%20Down%20List%20Based%20on%20Previous%20Column's%20Drop%20Down%20List%20Selection%20-%20Categories%2FSub-Categories%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-353548%22%20slang%3D%22en-US%22%3EHello%20Donna%2C%3CBR%20%2F%3EThe%20dynamic%20dependent%20drop-down%20list%20solution%20for%20you%20is%20similar%20to%20my%20reply%20here%3A%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2FData-validation-lists-flow-chart%2Fm-p%2F352333%23M25764%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2FData-validation-lists-flow-chart%2Fm-p%2F352333%23M25764%3C%2FA%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-894961%22%20slang%3D%22en-US%22%3ERe%3A%20Drop%20Down%20List%20Based%20on%20Previous%20Column's%20Drop%20Down%20List%20Selection%20-%20Categories%2FSub-Categories%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-894961%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDear%20Twifoo%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20make%20same%20drop%20down%20lists%20with%20some%20Financial%20Data%20having%20Heads%20in%26nbsp%3BAny%20Column%26nbsp%3Band%20Sub%20Accounts%20in%26nbsp%3BNext%20Column.%20I%20have%20seen%20your%20Maths%20Tracker%20workbook%20but%20it%20is%20not%20showing%20complete%20formula%2Frange%20in%20source%20while%20applying%20data%20validation.%20Can%20you%20plz%20explain%20how%20can%20I%20relate%20Heads%20from%20one%20column%20to%20Sub%20Accounts%20in%20different%20columns.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-895030%22%20slang%3D%22en-US%22%3ERe%3A%20Drop%20Down%20List%20Based%20on%20Previous%20Column's%20Drop%20Down%20List%20Selection%20-%20Categories%2FSub-Categories%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-895030%22%20slang%3D%22en-US%22%3ETo%20see%20the%20details%20of%20the%20named%20formulas%2C%20paste%20the%20list%20of%20defined%20names%20by%20following%20the%20instructions%20in%20this%20link%3A%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fexcelribbon.tips.net%2FT007731_Printing_a_List_of_Named_Ranges%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexcelribbon.tips.net%2FT007731_Printing_a_List_of_Named_Ranges%3C%2FA%3E%3C%2FLINGO-BODY%3E
DonnaE
New Contributor

Any help is appreciated on the following.  Thanks!

 

I'm setting up a sheet with columns to categorize/sub-categorize data.

I'd like a Category Column which has a Drop-Down List and a Sub-Category Column with a Drop-Down List based on the selected "Category" from the previous Drop-Down List.

 

Basically, if my lists look as follows below (Category List/Sub-Category List), how do I display the Sub-Category Drop-Down List for the corresponding Category (only) without having to include *all* Sub-Categories to choose from?

 

  • If "Category Column" Drop-Down Selection = Policy, then
    • "Sub-Category Column" Drop-Down Selection = only the option of....
      • "Personnel, Product, Refund/Cancellation, Safety, Security, Payroll, Attendance"

 

Categories Drop-Down List:

  • Promotions Category
    • Sub-Categories for Promotions Category:
      • Corporate
      • Membership Based
      • Customer Type
      • Sale
  • Policy Category
    • Sub-Categories for Policy Category:
      • Personnel
      • Product
      • Refund/Cancellation
      • Safety
      • Security
      • Payroll
      • Attendance
  • System Category
    • Sub-Cateogories for System Category:
      • Update
      • Enhancement
      • Replacement
      • Alert
  • Process Category
    • Sub-Categories for Process Category:
      • Call Handling
      • Product Sales
      • Requests
      • Complaints
      • Issuing Compensation
6 Replies

Hello Donna, 

Except that there are only 2 drop-down lists herein, the attached file is exactly the same as that which I uploaded in this link, which has 3: 

https://techcommunity.microsoft.com/t5/Excel/Dependent-Drop-down-List-Formula-for-only-showing-cells...

Cheers,

Twifoo

Thanks so much for the helpful direction, Twifoo!

@Twifoo 

Dear Twifoo

 

I want to make same drop down lists with some Financial Data having Heads in Any Column and Sub Accounts in Next Column. I have seen your Maths Tracker workbook but it is not showing complete formula/range in source while applying data validation. Can you plz explain how can I relate Heads from one column to Sub Accounts in different columns.

To see the details of the named formulas, paste the list of defined names by following the instructions in this link:
https://excelribbon.tips.net/T007731_Printing_a_List_of_Named_Ranges
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies