Seems working. which one is better.
1. what is dependent drop down starts from C5, the one with dd_....
2. If possible would you explain the way your formula works in steps. Also can you clarify what this step does in your formula for me to understand N28,dd_TYPE_LOOKUP
In the short term what is better is what works for you given where you are on your Excel Journey.
I am not sure this is the place to get into it but I will give it a quick try. In the long term, the aim is to minimise what is "hard coded" in the formula minimising the need to modify or maintain the formula in the future.
You can to this by
- Replacing constants with cell references or even better range named Cell references
- Replacing Cell ranges with Table Structured references which auto-expand and contract to fit the content
- Using vlookup / xlookup etc instead of multiple if then else statements
In the original method:
1) What happens when you have more data in column B ? Then the Data Validation / Drop Down list for TYPE will break, it will continue to only pick up rows 5-12
2) In your logic and formula, you have mixed data in the same TYPE list (Column B). Rows 5 to 8 need the Validation data in Column E and Rows 9 to 12 need the Validation data in Column F (G in my version). What happens when you want the 5-8 rows to be 5-10 ? The formula breaks.
Mitigate by:
1) Adding more data to B
Tables auto expand, so if your formula uses a table structured reference especially for TYPE it will automatically expand and contract.
We only want the data from the body (not the heading) and only the Column called Type so the reference is tbl_TYPE[TYPE].
You can't use that in the Data Validation box so I have created a range name dd_TYPE which refers to tbl_TYPE[TYPE]. (apparently I could have by passed that and used =Indirect(tbl_TYPE[TYPE], in the validation box.
2) Mixed logic in B
My TYPE table consists of 2 columns. Now instead of hard coding that rows 5-8 need different treatment to rows 9-12, you can control that based on what you put in the second column.
So you can add rows, sort rows, change the category of a row, without breaking the formula.
The formula now uses a vlookup to get the value in that 2nd column and uses that value to get Columns E (tbl_SLEEVE via dd_SLEEVE) or Column F (G in my version tbl_OTHERS via dd_OTHERS) for the 2nd Validation List - referred to as a "Dependent Drop Down List"
Using the Table Structured Reference tbl_Type picks up both data columns but for use in the Validation Formula I have mapped that to dd_TYPE_LOOKUP.
The VLOOKUP(N28,dd_TYPE_LOOKUP,2,FALSE), uses the TYPE entered into N28 to get the value in the 2nd column in tbl_Type which in turn gets SLEEVE or OTHERS table for the 2nd Drop Down in O28
Additional resources:
The 1st 8:15 mins of this (Leila Gharani XelPlus) will show you using tables in Validation Lists
SMART Drop Down Lists XelPlus
For Dependent Drop Down Lists using Table (Debra Dalgleish Contextures) - the video is 8 mins - I think I have sent you this before
Dependent Drop Down Lists Contextures
