• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Q: Advice Needed: Efficient way to build 3-Level Dependent Drop-Down Lists (Category > Subcategory > Pre-set Notes)

BonitaNut

BonitaNut

New Member
Please help!
I am building a personal budget spreadsheet and am looking for the most robust/efficient way to create a multi-level, dependent data validation structure in my "Main Transaction" table.

I find myself overwhelmed by the different methods online (Legacy INDIRECT vs. the newer FILTER/Spill functions) and would appreciate guidance on the best approach for my specific data structure.
My Environment:
    • Excel Version: [e.g., Excel 365 for Windows, Excel 2021]
    • Goal: To have Column F filter based on Column E, and Column G filter based on Column F.
The Structure:
    • Column E (Main Category): Standard Data Validation list.
    • Column F (Subcategory): Dependent on Column E.
      • Edge Case: One of my main categories ("Groceries") has no subcategories. I need to ensure the drop-down doesn't throw an error or allow invalid data in this case.
    • Column G (Notes/Description): Dependent on Column F.
      • I have specific recurring notes for certain subcategories (e.g., if I select "Auto Insurance," I want the dropdown to offer "Progressive").
Sample Data Hierarchy:
    • Set 1: Transportation (Main) -> [Gasoline, Auto Insurance, Auto Repair] (Subs) -> If "Auto Insurance" is selected, Note option: "Progressive".
    • Set 2: Personal & Family Care (Main) -> [Personal, Pet Care] (Subs) -> If "Pet Care" is selected, Note option: "Auto-Ship".
    • Set 3: Groceries (Main) -> [N/A or Blank] -> Notes: [Blank or Free text].
    • Set 4: Shopping (Main) -> [Hobbies, Electronics, Clothes] (Subs) -> If "Hobbies" is selected, Note option: "Goodwill".
My Questions:
Q1: Should I use Named Ranges with the INDIRECT function, or is there a cleaner way using XLOOKUP or FILTER (Dynamic Arrays) to handle the lists?
Q2: How do I best handle the "Groceries" category so the Subcategory list appears blank or greyed out rather than showing an error?
Q3: If there is a website or tutorial that explains this easily, please let me know.
Thank you so much your time and help!
 
Do You have an Excel-file, which has those You wrote?
You can attach it with below button [ Attach files ]
You should attach it OR same kind of sample Excel-file which has same kind of data, but like sample data.
We do not need Your real data here.
With a sample Excel file others has possible to see - what do You really have there.
 
vletm said:
Do You have an Excel-file, which has those You wrote?
You can attach it with below button [ Attach files ]
You should attach it OR same kind of sample Excel-file which has same kind of data, but like sample data.
We do not need Your real data here.
With a sample Excel file others has possible to see - what do You really have there.
Click to expand...
Sure!
Let me try to attach and upload.
 
vletm said:
Do You have an Excel-file, which has those You wrote?
You can attach it with below button [ Attach files ]
You should attach it OR same kind of sample Excel-file which has same kind of data, but like sample data.
We do not need Your real data here.
With a sample Excel file others has possible to see - what do You really have there.
Click to expand...
OK!
I have the column with Main Spending Categories done, but I cannot understand the subcategories (Levels 2 & 3).
Please help.
Thank you so much.
Excel file - example below.
Hopefully, this works?

EXCEL FILE LINK BELOW IN RED:
HELP Dependent Drop Downs in Excel Dynamic & Multiple
Please let me know if you are unable to see the file?
 
Last edited:
Why You used ... Dropbox?
Is it over one MB?
You could attach Your sample file smoother way - that it's possible to see & use without any links.
Do Your sample file match with Your original writings?
 
vletm said:
Why You used ... Dropbox?
Is it over one MB?
You could attach Your sample file smoother way - that it's possible to see & use without any links.
Do Your sample file match with Your original writings?
Click to expand...
I used Dropbox because I thought we used Dropbox to share files/worksheets.
I'm sorry if I was mistaken.
Were you able to see the link/Excel file through Dropbox?
Thank you for your help.
 
Can't you use the attachment button instead? Nobody really wants to follow an external link. The button will be at the foot of the reply window.

1763740246791.png
 
vletm said:

BonitaNut​

As written with my 2nd reply.
View attachment 91044
Was there something, why did You step it?
Click to expand...

AliGW and Vletm,
I apologize for my misunderstanding.
I've attached the example Excel file below.
I would greatly appreciate any help and suggestions (the easiest would be best for my intermediate skill level). :)
If I need to do anything else (or differently) as far as providing the file, please let me know.
Thank you for your patience and understanding.
 

Attachments

You must log in or register to reply here.
Back
Top