How to create auto-populate field based on criteria selected from 2 lists, Index and Match?

[drax1]

Hi all,

I need to find away to auto populate a field with a specific description based on the following

I have a row that contains 3 columns, column A has a drop down box with 2 choices. Column B has a drop down list with 10 options. Column C needs to auto populate based on the combination of choices made in column A and B

Column/Field A1 has 2 options that are selected from a drop down list, they are Alberta and Manitoba
Column/Field B1 has 10 different choices that are selected from a drop down list, they are the course code names to 5 different courses. There is an old course code and a new course code for each course so 2 course code names for each course, 10 course codes for 5 courses. Each of the 5 courses not only have 2 course numbers (old & new) they also have 2 different course names. The name of the courses are strictly related to the selected province and not the course code.
Column/Field C1 needs to auto=populate with the correct course name based on the combination selected in column A (Province) and Column B Course Code.

My data table looks like this

---Column B List---- --Column C Description--
Course Codes Manitoba Desc Alberta Desc
column A9 Column B9 Column C9 Column D9
B1005 6700 A Accounting Practical Accounting
B1006 6800 Law 1 General Law
B1007 6900 Nursing 1 Practical Nursing
D4000 8500 Drafting Basic Drafting

Example:
A1 I select Alberta from a drop down list
B1 I select course D4000
The formula identifies to use the Alberta description for D4000 "Basic Drafting" This is auto-populated in to C1

Thanks again for your help!

 

[oldbrewer]

location

area1 courses

area2 courses

course names

area1 or area2

C1001

C2001

C1001

law

C1002

C2002

C1002

accounting

C1003

C2003

C1003

nursing

C1004

C2004

C1004

welding

C1005

C2005

C1005

carpentry

C2001

history

C2002

geography

C2003

french

C2004

italian

C2005

spanish

choose area

area1

choose course

C1004

name of course

welding

can you restate your problem in terms of this data please ?

<colgroup><col><col span="8"><col><col><col><col><col><col span="4"></colgroup><tbody>
</tbody>

 

[drax1]

HI thanks for your help, I hope my changes below help clarify my question. Each course has the possibility of 2 course numbers and depending on the location has two different names. Thanks again

location List

Old course #

New course #

Alberta Name

Manitoba name

Manitoba

C1001

C2001

Basic law

law

Alberata

C1002

C2002

Accounting 1

accounting

C1003

C2003

Practical Nursing

nursing

C1004

C2004

Weliding 1

welding

C1005

C2005

Basic Carpentry

carpentry

Choose area

Alberta

Choose course #

C2005

Name of Course

Basic Carpentry

can you restate your problem in terms of this data please ?

<tbody>
</tbody>

Thanks

 

[oldbrewer]

	Old course #	New course #	Alberta	Manitoba
	C1001	C2001	Basic law	law
	C1002	C2002	Accounting 1	accounting
	C1003	C2003	Practical Nursing	nursing
	C1004	C2004	Weliding 1	welding
	C1005	C2005	Real Basic Carpentry	carpentry
	C2001		Basic law	law
	C2002		Accounting 1	accounting
	C2003		Practical Nursing	nursing
	C2004		Weliding 1	welding
	C2005		Real Basic Carpentry	carpentry
	Choose area	Alberta
	Choose course #	C2005
	Name of Course	Real Basic Carpentry	######
	###########
	=OFFSET($E$3,MATCH(F17,$E$4:$E$13,0),MATCH(F16,G3:H3,0)+1)
	I had to put the new course numbers below and duplicate the course list

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[drax1]

This absolutely works, thank you very much!

Is there anything I can do that will allow me avoid the duplicate course list. In reality I have many more courses and the duplicate list becomes a little unruly

 

[oldbrewer]

Old course #	New course #	Alberta	Manitoba
C1001	C2001	Basic law	law
C1002	C2002	Accounting 1	accounting
C1003	C2003	Practical Nursing	nursing
C1004	C2004	Weliding 1	welding
C1005	C2005	Real Basic Carpentry	carpentry
Choose area	Alberta
Choose course #	C2005
Name of Course	Real Basic Carpentry
	the following macro finds the correct course
	For j = 1 To 2
	For k = 1 To 6
	Sum = k
	If Cells(j, k) = Cells(14, 2) Then GoTo 100
	Next k
	Next j
	100 If Cells(13, 2) = Cells(1, 3) Then y = 3 Else y = 4
	Cells(15, 2) = Cells(Sum, y)
	End Sub

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[drax1]

Unfortunately the users of this form will give me the deer caught in the headlights look If they need to use macros. I think I will work with your initial suggestion. I was hoping excel would have some sort of simple If And then statement. Like [(if Column A = this) and (Column B = that) then (display the corresponding data (description) related to column A and B in the table in column C)]

Takes me back to my days working in basic... lol

Thanks again for your help.

 

[oldbrewer]

remember - you can put a large red rectangle saying "click me to find your course" and obviously attach the macro to that, and although I am not sure how to do it you can run a macro totally automatically when the course number is entered...........I am 69 and after fortran I learned basic........

 

[drax1]

I remember the cards with the holes in it right? Thanks again for all of your help

 

[oldbrewer]

hollerith cards