populate checklist from column in other tab

Question

Lembach Author

Level 1

0 points

populate checklist from column in other tab

Hi,

I'm working on a set of data that I've inherited. Please bear with me as I'm not exactly sure how to ask this - but...

Tab A has a list of colleges (name address, contact info)

Tab B has list of teachers (again more info)

On the teacher tab, I want to specify which colleges they are credentialed to teach at. I'd like this list to be populated from Tab A. I've set up a pop-up menu on other categories, but it won't work for this, as I'd like it to glean info (that will be updating) from Tab A, and also allow multiple choices - Teacher credentialed to teach at multiple colleges.

Conversely, I'd like to be able to click on a college and see what teachers are credentialed.

Can Numbers do this, if so how?

Thanks in advance,

Lembach (is staying)

Mac mini, Mac OS X (10.0.x)

Posted on May 29, 2015 7:54 AM

Reply

Answer 1

SGIII

Level 8

35,276 points

May 29, 2015 8:58 AM in response to Lembach

If I understand correctly, you may have more than one teacher associated with each college, and you may have more than one college associated with each teacher. This is a "many-to-many" relationship that relational databases (including Filemaker) handle well. With "array formulas" Excel can also be stretched to handle these types of problems. But Numbers doesn't have (most) array formulas. So, while this may be possible in Numbers (ingenious solutions are common here in the Support Communities) I'm thinking Numbers may not be your best tool for this.

SG

Reply

Answer 2

t quinn

Level 5

7,043 points

May 29, 2015 9:17 AM in response to Lembach

Hi Lembach,

SG's point is a good one. You might be wanting to push Numbers further than is practical.

If you are entereing all data into the Colleges table, it should be possible to feed that info to your Teachers table. Since the Colleges table already shows the teachers accredited, that part is covered.

quinn

Reply

Answer 3

Lembach Author

Level 1

0 points

May 29, 2015 10:05 AM in response to t quinn

Thanks Quinn and SG. So I guess i could simplify it, and just list the colleges a particular teacher is credentialed at. How would I go about creating a selectable list populated from colleges tab?

Thanks again!

Reply

Answer 4

t quinn

Level 5

7,043 points

May 29, 2015 10:09 AM in response to Lembach

Hi Lembach,

How are you going to list teachers in your Colleges table? You can post a screenshot.

What do you mean by "selectable list"?

quinn

Reply

Answer 5

Lembach Author

Level 1

0 points

May 29, 2015 11:01 AM in response to t quinn

So on sheet list, I want to populate "affiliations" with the Universities in sheet one. I'll be adding schools so I want a relationship between affiliations and University.

By selectable list, i guess I mean one than I can choose multiple Schools. The pop up list option apparently only allows one choice. Radio list, I guess?

Thanks again

Reply

Answer 6

SGIII

Level 8

35,276 points

May 29, 2015 1:10 PM in response to Lembach

Here's a possibility:

The University and Teacher tables are where you would put the relatively "permanent" information. The Matchups table is where you list the affiliations. The Lookup by University and Lookup by Teacher tables are populated automatically from the other tables when you choose the value in cell A1.

The formulas:

University table: no formulas

Teacher table: no formulas

Matchups table:

No formulas in A and B.

C2, copied down the column: =A2&COUNTIF(A$1:A2,A2)

D2, copied down the column: =B2&COUNTIF(B$1:B2,B2)

Lookup by University table:

A1 has no formula, but is formatted as Popup Menu to show how you could optionally add a "drop down list" type feature.

B3, copied down the column: =IFERROR(INDEX(Matchups::$A,MATCH($A$1&ROW()−2,Matchups::$D,0)),"")

C3, copied down the column: =IFERROR(INDEX(Teacher::B,MATCH($B3,Teacher::$A,0)),"")

D3, copied down the column: =IFERROR(INDEX(Teacher::C,MATCH($B3,Teacher::$A,0)),"")

Lookup by Teacher table:

B3, copied down the column: =IFERROR(INDEX(Matchups::$B,MATCH($A$1&ROW()−2,Matchups::$C,0)),"")

C3, copied down the column: =IFERROR(INDEX(University::B,MATCH($B3,University::$A,0)),"")

D3, copied down the column: =IFERROR(INDEX(University::C,MATCH($B3,University::$A,0)),"")

If you try this, I suggest setting all the tables up on the same sheet (tab). Then when you got things working as you want, cut and paste tables to separate sheets as needed.

SG

Reply