Drop down selection list : depending on cell value of adjace

suhas_shah

New Member
Joined
Sep 23, 2006
Messages
39
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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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

New Member
Joined
Sep 23, 2006
Messages
39
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
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Assuming Code in Col.A, subcode in Col.B in Submast sheet...
try
Code:
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
 

Forum statistics

Threads
1,141,626
Messages
5,707,489
Members
421,510
Latest member
haroonstr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top