• 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.

Dependent Data Validation

E

excelCoder

New Member
Hello guys,

I am using this tutorial to practice my excel skills in data validation: http://chandoo.org/wp/2012/11/27/extract-subset-of-data/


As you can see, I have implemented dependent lists. However, I want to implement dependent data validation in my sheet from the top down .

I have implemented this using the INDIRECT function in my data validation in Range. However this does not really work.

Any recommendations how to fix that?

I appreciate your answer!!!
 

Attachments

  • test.xlsm
    test.xlsm
    1,008.7 KB · Views: 9
  • Capture.PNG
    Capture.PNG
    30 KB · Views: 10
@excelCoder

To implement tutorial suggested by @bobhc , first you have to define your dependent list. Like

Code: 
Product                  Computer                Electronics                           
Computer              Motherboard          LED TV
Electronics            Processor                DVD Player
                                RAM                          Washing Machine
                                Hard Disk

Regards,
 
Thx for your anwer! Any sample sheet which you could share, cause I really do not know what you mean...

I appreciate your answer!
 
Hi ,

I think there is some confusion here ; the workbook you have uploaded does not use the dependent data validation feature at all.

You have filter criteria in the range C5 through C9 ; the selection made in C5 using the drop-down list does not in any way determine the list which will be shown in C6 for the user to select from ; the regions East , West , North and South are in no way dependent on which product has been selected in C5.

Your raw data has every product being sold in every region , so I don't think dependent data validation lists are relevant here.

Be that as it may , the use of INDIRECT($C$5) will work only if you have already created named ranges called Desktop , Laptop , Accessories ,.... so that if Desktop is selected in C5 , the named range called Desktop can be used as the drop-down list for C6. At present you have not created any of these ranges.

Narayan
 
@NARAYANK991

Thx a lot for your answer!!!

The reason why I am asking you this question is, that I want to have a final list of customers in the Call id col(after filtering), which I can use to process their data. Any recommendations for that?
 
@excelCoder

As @NARAYANK991 Sir has pointed you in right direction, the data that you have in your test file is not suitable for dependent data validation as in this case you have to develop a lot of dependent list---- like - Region depending upon product than Customer type depending upon region than Agent depending upon ..... hmmm there the complexity will enter in the problem. I hope now you can understand why we are saying that this data is not suitable for dependent data validation.

But to make you understand,how the concept work, please have a look of attached file. I had cut short your raw data from 10000+ rows to 200 rows for just example.

1. Data cut short (As this is just an example)
2. Made Only one stage of dependent list i.e. Call ID depending upon Product.
3. On MASTER sheet in column G extracted unique product from the data.
4. Next made dynamic ranges for getting Call ID based on product in Column I,K,M,O,Q,S.
5. Converted these extracted values to tables with the names as product type as mentioned in row 1. (Kindly refer name manager)
6. Created data validation on Filter Sheet in cell C5 for getting product names.
7. Created data validation in cell C9, depending upon Value in C5, for getting Call ID. (Please refer formula in data validation dialouge box).

If you will increase the data, extend formulas on master sheet and adjust the range in it. Also, adjust the tables ranges on MASTER Sheet. You will have dynamic dependent data validation list.

Just advise if you have any issue on this.

Regards,
 

Attachments

@Somendra Misra Thx a lot for your answer! Its awesome work from you!!! However, isn`t there a simpler way to achieve this? Probably with comboboxes, or sth. similar? I looked up "dependent comboboxes" but did`t find anything, what goes in the direction of filtering like that...
 
@excelCoder

If the dependent list are small with few options this is a easy task, but as the data you represents it will not be easy. you have to extract items first than you have to allocate them to different product.

I would suggest you to learn the trick with a small example and than apply it on your actual project.

Even using combobox, you have to pupolate those comboboxes with lists where again data extraction will come into picture, so instead of formulas some ten's lines of codes will be doing the task.

Regards,
 
@Somendra Misra Thx a lot for your answer! Will definitely do this with the lists to practice it.

Is it also possible to get all selections of the customer field?

Even using combobox, you have to pupolate those comboboxes with lists where again data extraction will come into picture, so instead of formulas some ten's lines of codes will be doing the task.
Click to expand...

However, any recommendations for tutorials to do it via vba?
 
Last edited:
@Somendra Misra

Thx a lot! Will definitely try that!!!

The thing why I want to do that is that I want to have the customerlist which is in the dropdown, which depends on the product etc. Is there any way to get this from the autofilter or is this only possible via the comboboxes?
 
@excelCoder

See here also in your above case both Product list & customer list is dynamic and may increase or decrease in future ( it is not fixed) so you have to go with dynamic named ranges & dynamic formulas. As far as combobox are concerned go through those tutorials try some thing and come back if you face any difficulties.

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