Drop down selection list : depending on cell value of adjace

[suhas_shah]

have a excel file having 3 different sheets.

Sheet 1 : Partmast
Sheet 2 : Submast
Sheet 3 : Entry

In sheet "Partmast" all Part Codes & Names are stored with column heading CODE and NAME

In Sheet "Submast" all Part Code with SubCodes are stored with column heading CODE and SUBCODE.

In sheet "Entry" i am entering the data in column A : Code, Column B : Sub Code.

I am validating the data by using vllokup and storing "T" in column C if the match is found and "F" if the match is not found.

If value of Column C in row is "F" on PRESS OF A BUTTON , it should populate a list box / drop down list of all related sub codes for the Code stored in the column A. The list should not include the sub codes not pertaining to Code stored in Column A

For reference i have attached the excel sheet.

Entry Sheet
Row Column A Column B Column C
CODE SUBCODE VALID
1 500001 4 T
2 500003 4 T
3 500004 2 T
4 500005 3 T
5 500001 45 F
6 500001 54 T
7 500001 67 T
8 500001 59 T

In above case Row 5 : Column C is "F" . Now user will press a button , which will populate a list box having all sub codes pertaining to CODE no. stored in the row i.e. Row 5 Column A = 500001

Sample submast data is as below

sUBMAST sHEET
COLUMN A COLUMN B
CODE SUBCODE
500001 44
500001 67
500001 4
500001 54
500001 59
500002 5
500002 65
500002 79
500003 4
500004 2
500005 3

Hope i have explained properly.

Can anyone help.

Thanks in advance

Regards

Suhas

 

[SydneyGeek]

I'm going to answer this generically, to show the principle. You can then adapt it to suit.

Say you have 4 sublists. Call them Spanners, Tubes, Hammers, Fasteners.
Each of those lists needs to live in its own column on a worksheet somewhere -- you can hide it if you like. Call the sheet Lists in this example.

You need a master list that has the names of the sublists as its contents -- in this case,

Spanners
Tubes
Hammers
Fasteners

This list can live on the same sheet as the sublists, with its own column. Call it Master.

Each of these lists needs to be defined as a dynamic list so you can add new items without needing to redefine the lists all the time.

Say that Master is in Column A of Lists, with heading in A1 and first item in A2. Insert > Name > Define. In the top line of the dialog, type Master (or select it from the list if you have already created the name).
In the bottom entry area, create this formula:
=OFFSET(Lists!$A$1,0,0,COUNTA(Lists!$A:$A)-1,1)
Read the Help files to get the background on this formula.
Click Add

You can do the same for the other lists at the same time. Just change the column reference to suit, and remember to use absolute references.

OK -- back to the workbook.

Say you're in A2 of a worksheet (not Lists). Data > Validation. Select List instead of Any Value.
In the Reference text area, type =Master
Click OK.

This lets you pick the names of the 4 sublists, and nothing else.

In C2, you want to select the sublist items.
Data > Validation again, also List, but this time the reference is
=INDIRECT(A2)

This will switch to the sublist whose name you selected in A2. If you need to create more sublists you do this:
1. Create the list
2. Create the dynamic name
3. Add the new name to the Master list

Hope this helps

Denis

 

[suhas_shah]

The same is possible, but my problems if there are around 800 codes and each code is having 35-40 sub codes. Now creating named list is going to be very time consuming and if new codes come in then again create the lists.

Is there any other way to do it.

Regards

Suhas

 

[Krishnakumar]

Hi,

See: DependentDV_1

DependentDV_2

 

[jindon]

Assuming Code in Col.A, subcode in Col.B in Submast sheet...
try

Sub test()
Dim r As Range, a, i As Long, txt As Sting, myTxt
a = Sheets("submast").Range("a1").CurrentRegion.Resize(,2).Value
With Sheets("Entry")
   For Each r In .Range("c1",.Range("c" & Rows.Count).end(xlUp))
      If r.Value = "F" Then
         myTxt = r.Offset(,-1).Value
         For i = 1 To UBound(a,1)
            If myTxt = a(i,1) Then txt = txt & a(i,2) & ","
         Next
         With r.Validation
             .Add Type:=xlValidationList, Formula1:=Left(txt,Len(txt)-1)
         End With
      Else
         r.Validation.Delete
      End If
   Next
End With
End Sub