Dependent (cascading) drop down list in Excel

Hello Ganesh,

I'll assume that you have read post #8 in your last thread so I believe that you should be able to use this Worksheet_Change event code to do the task:

Private Sub Worksheet_Change(ByVal Target As Range)
        
    If Intersect(Target, Range("G2")) Is Nothing Then Exit Sub
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Value = vbNullString Then Exit Sub
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    Me.Range("G5", Me.Range("G" & Me.Rows.Count).End(xlUp)).Clear
        
            With Me.[A1].CurrentRegion
                    .AutoFilter 1, Target.Value
                    .Offset(1, 2).Copy Sheet1.[G5]
                    .AutoFilter
            End With
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
 
End Sub

To implement this code:

- Right click on the sheet tab.

- Select "View Code" from the menu that appears.

- In the big white code field that then appears, paste the above code.

Test this code in a copy of your workbook first.

Remove anything that you have in cells G9 and G11 to clear the space.

Once you make a selection from the drop down list in G2, the code will instantly paste the relevant information from G5 down.

I hope that this helps.

Cheerio,

vcoolio.

Hi vcoolio Sir,

 The Above Works good . But  i want a drop down list for route card numbers as well based on job card no

The job numbers and route card numbers are taken from another sheet(main database).

Kindly update the code as per attached sample sheet(main database).

link to download the sheet: https://wetransfer.com/downloads/689eb6471e545d4d112de01b8ba0bc0d20230803055222/16305b

Regards,

Ganesh

Hello Ganesh,

Based on your comment in your opening post:

From cell G5, I wish to list the Route Card numbers associated with the selection made from the drop down in cell G2.

 you have your answer in post #1.

You do not mention anything else. However, are you asking basically for the reverse as well or just to show the results in drop down format in cell G5 rather than a fixed list?

BTW, the link that you supplied in your opening post has expired and is unavailable.

Cheerio,

vcoolio.

Hi vcoolio sir,

please see my sample sheet (Main database) and apply the above the code in machine shop sheet.

I want Route card numbers as a drop down list.

link : https://wetransfer.com/downloads/689eb6471e545d4d112de01b8ba0bc0d20230803055222/16305b.

Regards,

Ganesh

Hello Ganesh,

That link is not working and won't allow me to download it.

Please try again with a new link and I'll have a look at it for you tomorrow. I think that I now understand your intent.

Cheerio,

vcoolio.

Thanks HelpiOS.

It was very late last night when I looked at Ganesh's last post and I didn't spot the full stop.

Anyway, good day once again Ganesh.

The code below will do the task for you but firstly you will need to create a named range for the  Route Card Nos. So, in the MAIN DATABASE sheet, the best place for the named range would be out of sight. I've used Column AH in the code.

Hence, in your workbook, go to Column AH and select twenty cells from AH1 to AH20 (I think that twenty cells should suffice). Go over to the Name Box just above Column A and click in it. Type in RCN then press Enter. Click on the down arrow in the Name Box and from the drop down click on RCN. This is the name of the named range and you should see that the first twenty cells in Column AH will be high-lighted but empty.

Next, go to the Machine Shop sheet module by right clicking on the sheet tab then selecting "View Code" from the menu that appears. Once in the module, remove any code that you already have there and copy/paste the following code:-

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim wsMD As Worksheet: Set wsMD = Sheets("MAIN DATABASE")
    
    If Intersect(Target, Me.Range("B3")) Is Nothing Then Exit Sub
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Value = vbNullString Then Exit Sub
    
    wsMD.Columns("AH").ClearContents
    
    With wsMD.[A2].CurrentRegion
            .AutoFilter 2, Me.[B3].Value
               .Offset(1, 2).Resize(, 1).Copy wsMD.[AH1]
               
                      With Me.Range("I2").Validation
                            .Delete
                            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                                 Formula1:="=RCN"
                            .IgnoreBlank = True
                            .InCellDropdown = True
                            .InputTitle = ""
                            .ErrorTitle = "Error"
                            .InputMessage = ""
                            .ErrorMessage = "Please provide a valid input"
                            .ShowInput = True
                            .ShowError = True
                       End With
            .AutoFilter
     End With

    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
 
End Sub

ClearContents from cell I2, save and close the workbook with the .xlsm file extension.

On re-opening the workbook, go to the Machine Shop sheet and make a Job No. selection from the drop down list in B3. The relevant Route Card numbers will then appear as a drop down list in I2 once you click on this cell. Make your selection from the drop down to do the required task.

Each time that a different Job No. is selected from the list in B3, only the relevant Route Card numbers will appear in I2 as a drop down list.

It would be a good idea for you to create a copy of your actual workbook first and test all of the above before applying the new method to your actual workbook.

I hope that this helps.

Cheerio,

vcoolio.

Morning vcoolio sir,

Please take the named range in C  Column.

Hence, in your workbook, go to Column AH and select twenty cells from AH1 to AH20 (I think that twenty cells should suffice). Go over to the Name Box just above Column A and click in it. Type in RCN then press Enter. Click on the down arrow in the Name Box and from the drop down click on RCN. This is the name of the named range and you should see that the first twenty cells in Column AH will be high-lighted but empty.  --- sorry sir. I don't understand this point.. if possible can you try this on my excel sheet. attached we transfer link below.

Link : https://wetransfer.com/downloads/4bfc57bc8cd6314f189a736bebb38c2d20230805051346/8a57ce

if possible please work on the above sample sheet and send a link  to download.

Regards,

Ganesh

Hello Ganesh,

I think that you have misunderstood the instructions.

Firstly, go back to the MAIN DATABASE sheet and clear cell A1. I noticed that you have placed RCN in it. This is the wrong place for it.

Next, go to Column AH and completely clear it of anything that you have placed in it.

Next, even though the column is empty, select cell AH1 down to AH20 (you'll note that these cells are now high-lighted). Leave them like that.

Next, go to the Name Box. This is the little box directly above cell A1 which has a little drop down arrow in it.

Click in the Name Box and type RCN into it then press ENTER. This is now the named range we need for the code to work. To check that you have done this correctly, click on any cell to move away from the Name Box and you should see the cell reference displayed in the Name Box. Click on the little down arrow in the Name Box and you will see RCN displayed. Select RCN and you will be taken over to Column AH which should still be blank but high-lighted. This means that all is working as it should.

Next, I noticed that you have made changes to the code I supplied in post #7, so once again, copy and paste it exactly how it is in post #7 and make sure that it is pasted into the Machine Shop worksheet module. Again, save and close the workbook.

I could do all this for you in your last supplied sample but I want you to carry out the various steps so that you will hopefully understand the workings of the code.

Cheerio,

vcoolio.

Dear vcoolio sir,

You're Just excellent .. Thank you very very much for your step by step explanation and also thank you for your valuable time spending for me and the above given code working superb. I am getting different route card numbers in a drop down list based on job card no in main database. it helps me a lot. .. I got it what I required. Problem solved.

I am ever grateful to you sir.  

Regards,

Ganesh

Dear Sir,

1 more possibility to get wrong information in route card. lets say, If I select a job number but unfortunately I forgot to select the route card number(I2) in machine shop sheet, the old card information remains showing and going for next step(Printing or PDF step). that is wrong.

what should I do sir?

give me an idea or amend the code to erase the old route card no in I2 automatically when I select another job number in B3. then after I select the route card numbers from drop down list in I2. ( it will asks me to select route card no) 

Regards,

Ganesh

Hello Ganesh,

To clear the contents of I2 prior to further selections in B3, add this line of code:

Me.[I2].ClearContents

directly below this line of code:

wsMD.Columns("AH").ClearContents

I hope that this helps.

Cheerio,

vcoolio.

Dear vcoolio sir

Thank you very much once again.

Problem solved.

Regards,

Ganesh

You're welcome Ganesh. I'm happy to have been able to assist and thank you for your kind words.

BTW, if you want Users to be reminded to select a Route Card Number, you could place this line of code:-

MsgBox "Don't forget to select a Route Card number!", vbExclamation, "WARNING"

in between the first End With and .AutoFilter.

It's just a simple pop up message box.

Cheerio,

vcoolio.

Once again thank you very much sir.

Popup warning coming .

Regards,
Ganesh