how to display subjects taught by a specific teacher upon selection of the teacher name in a drop do

[janice fernandes]

there are 3 teachers in a school. Ann, ben and carl. Ann teaches math and
history, ben teaches math, history and science, carl teaches math, science
and drawing. suppose cell a1 contains a drop down box containing the three
names (ann, ben and carl). if i select ann in a1, cells a3 should display
math and cell a4 should display history. alternatively, if i select ben in
a1, cell a3 should display math, cell a4 should display history and cell a5
should display science. alternatively if i select carl in a1, cell a3 should
display math, cell a4 should display science and cell a5 should display
drawing. i hope i have made my question clear. thank you in advance for the
answer. also what is the way to store the teacher names with their related
subjects

is there a specific term to describe the above concept? - therefore I can
search in google, excel help sites using that term. Also can you suggest a
better title for better results

 

[flummi]

Here's a proposal:

A1 = validation list with keys in I2:I5 (first col of vlookup table)
B1:E1 = results from vlookup
Formula in B1 copied to C1:E1:

=IF(VLOOKUP($A$1;$I$1:$M$4;J1;FALSE)="";"";VLOOKUP($A$1;$I$1:$M$4;J1;FALSE))

Lookup table in

0 2 3 4 5
ann math history
ben math history science
carl math science drawing german
peter english drawing german

 

[Bob Phillips]

You have a response on JMT

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[flummi]

Sorry, hit the send button too early. :-(

Here's a proposal:

A1 = validation list with keys in I2:I5 (first col of vlookup table)
B1:E1 = results from vlookup
Formula in B1 copied to C1:E1:

=IF(VLOOKUP($A$1;$I$1:$M$4;J1;FALSE)="";"";VLOOKUP($A$1;$I$1:$M$4;J1;FALSE)­)


You may have to replace the semicolons with commas.

Lookup table in I1:M5

0 2 3 4 5
ann math history
ben math history science
carl math science drawing german
peter english drawing german

the numbers in the first row of the lookup table allow copying the
formula without modification.

Hans

 

[Tushar Mehta]

You have a response on JMT

What does that mean?

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Tushar Mehta]

If you are OK with Hans' answers, read no further. {grin}

Ideally, your data should be laid out in a table with 2 columns:
Teacher and Class.

Now, you can use a variety of techniques to find the relationship between
teachers and the classes they teach including filters and PivotTables.


--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions