We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Solved
dependent validation list using only the first 2 characters...possible in Excel or not?
Posted on 2016-08-24
I can set up a dependent dropdown validation list using Indirect..named ranges etc. However, is it possible to do the following..
in the attached file I have a list of 4 digit codes that starts with 52, then another that starts with 50. Is it possible to set up a dependent dropdown list that is set up so that when the user enters a code starting with 52 in one cell, that on the same row in a column beside it, a list is generated which only shows the descriptions and numbers for the codes starting with that e.g. in the attached file it should only show the entries in rows B1:B20.(in blue) Then if the user selects a number that starts with 50, they should only see the entries in B21:B27 (yellow).
Thanks
EE-dependent-dropdown-list---first-.xlsx
in the attached file I have a list of 4 digit codes that starts with 52, then another that starts with 50. Is it possible to set up a dependent dropdown list that is set up so that when the user enters a code starting with 52 in one cell, that on the same row in a column beside it, a list is generated which only shows the descriptions and numbers for the codes starting with that e.g. in the attached file it should only show the entries in rows B1:B20.(in blue) Then if the user selects a number that starts with 50, they should only see the entries in B21:B27 (yellow).
Thanks
EE-dependent-dropdown-list---first-.xlsx
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
3
2-
2
- +1
8 Comments
Active today
Like this?
EE-dependent-dropdown-list---first-.xlsx
EE-dependent-dropdown-list---first-.xlsx
Active 5 days ago
This does the job thank you :-) but I know the users...they want something that will just autopopulate with the required entries. Really what I want to know is: is it possible to do a dependent dropdown list just using the first two digits as the dependent factor. I don't think so but I'm more than willing to be proved wrong... :-)
Active today
That is what the selection in A1 does. The drop down lists in col. A and B are dependent upon the selection in A1.
Active 5 days ago
I know that and I can see that. But what the user wants is a scenario where the following happens.
She has set up a vlookup that autopopulates with the 4 digit codes. Then the users are to apply a sector to it e.g. 5000 narrows it down to the description of the codes that starts with 50 but in your solution they would have to pick 50 or 52 from the list...
She has set up a vlookup that autopopulates with the 4 digit codes. Then the users are to apply a sector to it e.g. 5000 narrows it down to the description of the codes that starts with 50 but in your solution they would have to pick 50 or 52 from the list...
Active today
Can you upload a sample workbook with the same layout as of your original workbook with all the formulas in place and highlight the cells where you want the drop down lists and then explain your logic based on the sample workbook?
agwalsh,
If I understand your request correctly, you would like to see an "Intellisense-like" feature like seen in table filters. The function behaves as a real-time filter that shows valid values that have sub-string matching of entered values.
What I'm not quite understanding is why a user would have to enter all four digits just to see the other codes that have the same first two.
-Glenn
If I understand your request correctly, you would like to see an "Intellisense-like" feature like seen in table filters. The function behaves as a real-time filter that shows valid values that have sub-string matching of entered values.
What I'm not quite understanding is why a user would have to enter all four digits just to see the other codes that have the same first two.
-Glenn
I've whipped up a possible workbook application that uses an array function, VLOOKUP, and conditional formatting to display results dynamically.
It has an input cell (A2) that can accept any input. Any codes in the "Data" sheet that have the same first two digits as this value will display in a table to the right along with their descriptions.
The formula to retrieve the Matching Codes is an array function ([Ctrl]+[Shift]+[Enter] required):
=IFERROR(INDEX(Data!$A$1:$
The formula for the Descriptions is simpler:
=IFERROR(VLOOKUP(B2,Data!A
You'll note that both look only as far as 40 rows; that can easily be updated.
Additionally, conditional formatting is used to only display table borders for matching values. The actual range of these formulas extends down to row 40 (as does the conditional formatting rule).
Hope this helps.
-Glenn
EE-dependent-dropdown-list-first.xlsx
It has an input cell (A2) that can accept any input. Any codes in the "Data" sheet that have the same first two digits as this value will display in a table to the right along with their descriptions.
The formula to retrieve the Matching Codes is an array function ([Ctrl]+[Shift]+[Enter] required):
=IFERROR(INDEX(Data!$A$1:$
The formula for the Descriptions is simpler:
=IFERROR(VLOOKUP(B2,Data!A
You'll note that both look only as far as 40 rows; that can easily be updated.
Additionally, conditional formatting is used to only display table borders for matching values. The actual range of these formulas extends down to row 40 (as does the conditional formatting rule).
Hope this helps.
-Glenn
EE-dependent-dropdown-list-first.xlsx
Active 2 days ago
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.
I have recommended this question be closed as follows:
Split:
-- Subodh Tiwari (Neeraj) (https:#a41768377)
-- Glenn Ray (https:#a41771081)
If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.
frankhelk
Experts-Exchange Cleanup Volunteer
I have recommended this question be closed as follows:
Split:
-- Subodh Tiwari (Neeraj) (https:#a41768377)
-- Glenn Ray (https:#a41771081)
If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.
frankhelk
Experts-Exchange Cleanup Volunteer
Featured Post
Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications. You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Suggested Courses
Course of the Month6 days, 1 hour left to enroll
688 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.