• 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

vletm said:
Where were places for third level?
... added one column for that option

Learnt that Category List2's 1st row cannot have any spaces within texts.

Check K1 link for details.
Click to expand...
Thank you so much!
I apologize - I think I did leave the 3rd level option out of the main spreadsheet. Thank you for adding that!

I'm so confused.
Wonder what I am not getting?
I know this is not that difficult, but I am struggling for some reason.

I greatly appreciate your time and help.
This means a lot to me.

Check K1 link for details.
GOT IT!

Please feel free to pass along any additional links or instructions.

THANK YOU SO MUCH!
 
Wonder what I am not getting?
Sit quiet and breath few times deep - repeat few times.

Google has many this kind of pages.
Please try to read.
K1:
Screenshot 2025-11-22 at 18.58.13.png
Even this could take more than one minute learn - be patient.
Sometimes You could walk outside - that helps many this kind of cases too.
 
vletm said:
Where were places for third level?
... added one column for that option

Learnt that Category List2's 1st row cannot have any spaces within texts.

Check K1 link for details.
Click to expand...
I'm struggling with:
1- Named Ranges and getting them set up properly.

I am converting the Main Categories, Subcategories, and Merchants into Tables before Named Ranges.
Is this a problem?

I'm not understanding the 1- Tables, 2- Formulas > Name Manager instructions.
Thank you again.
 
vletm said:
Wonder what I am not getting?
Sit quiet and breath few times deep - repeat few times.

Google has many this kind of pages.
Please try to read.
K1:
View attachment 91052
Even this could take more than one minute learn - be patient.
Sometimes You could walk outside - that helps many this kind of cases too.
Click to expand...
You are exactly right.
I've never done this before, so I need to be patient.
Thank you so much for your help, and I greatly appreciate the link.
I will read and follow.
Thank you!
 
If You would follow that pages information as I did (the 1st time) then You would get same result ... functionality.
These are something else than one second things.
It would take time to learn after learn to read.
It's same as - learn to walk before dream to run.
 
vletm said:
If You would follow that pages information as I did (the 1st time) then You would get same result ... functionality.
These are something else than one second things.
It would take time to learn after learn to read.
It's same as - learn to walk before dream to run.
Click to expand...
vletm,
I am probably trying to push beyond my Excel knowledge and skill by creating Tables and Named Ranges.
I need to start with something more for my skill level (even if I have to go back and revise if I add new information instead of it automatically updating with Tables).
I wanted to ask, please?
Q1: In the Example above that you helped with, did you use Tables or convert any columns to Tables?
I looked in Name Manager and did not see any Tables.
Am I correct?
I need to focus on Name Manager > Scope > Workbook.
Thank you again!
 
About Your question Q1 ... hmm?
You have there word or .... I did not use any of those.
I followed that links instructions ... if so, then where were something about those Your or-wondering?
Did You really try to do so?
Q: Am I correct?
No ... There was one table ( Table1 )... where did You look?
Formulas > Name Manager
Your Next steps ...
Try to do and follow that links sample.
Follow really means that You gotta do all steps.
If You'll miss any step then ... focus ... focus ... have a short walk and test again, please.
 
vletm said:
About Your question Q1 ... hmm?
You have there word or .... I did not use any of those.
I followed that links instructions ... if so, then where were something about those Your or-wondering?
Did You really try to do so?
Q: Am I correct?
No ... There was one table ( Table1 )... where did You look?
Formulas > Name Manager
Your Next steps ...
Try to do and follow that links sample.
Follow really means that You gotta do all steps.
If You'll miss any step then ... focus ... focus ... have a short walk and test again, please.
Click to expand...
I GOT IT!
OMG!
I got my Levels 1-3 figured out, and I was definitely misunderstanding quite a few steps.

I did it the most straightforward way (which I needed to do), and I got it!
I understand now how the Named Ranges are set up (I did this the most straightforward way).
I am also understanding how to use the Data Validation according to the Levels.
I am trying to keep notes, so I can remember to do it again!
LOL
Overall, it's not complex, but I am hoping it will work for my personal budget list.
Thank you so much for your time and help!
 
You must log in or register to reply here.
Back
Top