Populate multiple cells from single dropdown

Question

I have the need to create a spreadsheet that will populate multiple fields in the spreadsheet based on a drop down selection. I have looked at a threads on other sites regarding this and believe I have grasped the concept of it, however the examples given arefor a simple look up table with one line of data for each item.The look up table I am wanting to strip data from has up to 16 items which need to be shown, i.e.

 

Sheet 2 (VLookup Table).

Column A has "Jobtitle", the next column contains all the "Courses" relating to that job title.

 

Job Title	Courses
Team Leader	Ship Sea Safety
****	SHORT PHOTOGRAPHIC COURSE
****	ME	Paint Shop Manager (ME456E)	Navy
Assistant	Ship Sea Safety
****	Slinginer
****	Maintenance Management Systems Planner
****	First Aid Certificate Course

 

Sheet 1

A1 Dropdown will contain the Jobtitle and when selected i need ALL the courses relating to that job title to appear in the next column B.

 

Is this possible??

Answer 1

I dont think i have explained correctly what i am trying to achive, and having looked how i had things set out i have changed a few things.

Sheet one will be a live document of people who join the company and leave. However once they have left their info will remain on Sheet1.

Sheet 1 is set out as follows:

A	B	C	D	E	F	G	H
Name	Job Title	Emplyee No	Join Date	Courses	From	To	Live

• Column B has a dropdown list of job titles to choose from. there is a dropdown list in every cell.
• Column E has the following formula "=IF(ISNA(VLOOKUP(B2,Courses,2,FALSE)),"",VLOOKUP(B2,Courses,2,FALSE)). This is repeated in every cell of the column changing B2, B3, B4, B5 incrementally

Sheet 2 is set out as follows:

	A	B
1	Job Title	Courses
2	Team Leader	Ship Sea Safety
3	****	SHORT PHOTOGRAPHIC COURSE
4	****	Shop Manager
5	****	First Aid Certificate Course
6	****	Protection Team
7	****	Equipment Maintainer and Supervisor
8	****	Dvr Lic Cat D
9	****	Dvr Lic Cat B
10	****
11	Assistant TL	Ship Sea Safety
12	****	Operator
13	****	Equip Manager
14	****	System Operator
15	****
16	Driver	Ship Sea Safety
17	****	Dvr Lic Cat D
18	****	Dvr Lic Cat B
19	****	First Aid Certificate Course
20	****	Protection Team

What i am trying to achieve on Sheet 1 is:

As someone joins the company i input their details in the next available row i.e. Row 3, when i select the "Job Title" the correct number of Cells in Column are populated from Sheet 2 "Courses" with the course titles. As you can see different Job Titles have a different number of courses pertinent to them. Therefore if the first person joining was the Team Leader the next persons details would be annotaed 8/9 Rows down, and so on so forth.

At present with the the Vlookup formula only the first course is displayed.

Can this be done??

Answer 2

Hi,

If the blank cells in the Code and Job Title can be plugged in with the relevant codes and Job Title, then you may refer to my solution at this link - http://www.ashishmathur.com/lookup-where-search-string-appears-multiple-times/

Hope this helps.

Answer 3

Put this code behind sheet1

This is on the presumption that your dropdown is in cell A1 in sheet1

---

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address(0, 0) <> "A1" Then Exit Sub

Dim sht As Worksheet, rng As Range, r As Range, i As Integer

Set sht = Worksheets("Sheet2")

Set rng = sht.Range("B:B").Find(Target.Value)

If rng Is Nothing Or Target.Value = "" Then

Me.Range("B:B").ClearContents

Exit Sub

End If

Dim lngLastRow As Long

lngLastRow = rng.End(xlDown).Row - 1

If lngLastRow + 1 = sht.Rows.Count Then

lngLastRow = rng.Offset(0, 1).End(xlDown).Row

End If

Set rng = rng.Offset(0, 1)

Set rng = rng.Resize(lngLastRow - rng.Row + 1)

Me.Range("B:B").ClearContents

i = 0

For Each r In rng.Cells

Me.Range("A1").Offset(i, 1).Value = r.Value

i = i + 1

Next r

End Sub

Hope this works for you

Answer 4

Sorry i am unable to upload a picture of the table, the layout is very similar to this:

         A                              B                                   C

     Code                       Job Title                      Course

1234567              Team Leader	Ship Sea Safety
	SHORT PHOTOGRAPHIC COURSE
	Shop Manager
1356721                   Assistant	Ship Sea Safety
	Slinginer
	Maintenance Management Systems Planner
	First Aid Certificate Course

Answer 5

How is the lookup table layedout?