Hello, I'm working on creating an "opportunity sheet" in Excel. I have 9 instructors (teachers) and 105 topics to teach. What I'd like to do is to add all the topics in a spreadsheet and give them a quick way to see courses they haven't taught in the past.

So, there is a "sign-up" drop down list for topics to assign an instructor to, and I want them to be able to run a macro (or something) to look up previous courses (same spreadsheet but different tabs) and only show the courses they have NOT signed up for in the past so they can pick stuff they haven't yet taught. I realize it's easy enough on paper to figure out, but if I can make it easier for them to see their choices, they'll expand their topics.

Thanks!

Could you possibly post a dummy example of your workbook?
Would make it far easier to see exactly what you need.

Hope this helps, and thanks in advance.

I'm working on creating an "opportunity sheet" in Excel. I have 9
instructors (teachers) and 105 topics to teach. What I'd like to do is
to add all the topics in a spreadsheet and give them a quick way to see
courses they haven't taught in the past.

So, there is a "sign-up" drop down list ...
only show the courses they have NOT signed up for in the past ...

Here's one way to set up a workbook for this using Excel 2003.

Let's use four worksheets.

Sheet1 has the desired result: dropdown lists for all the teachers.

Sheet2 indicates which courses each teacher has already taught.

Sheet4 lists the courses for the dropdown lists.

Sheet3 has intermediate results for calculating Sheet4.

Start by entering the teachers' names in B1:J1 of all four sheets.

Next enter the course names in Sheet2!A2:A106. Now Sheet2 is a table of courses versus teachers.

For each course a teacher has already taught, enter the number 1 in the corresponding cell of Sheet2. Leave the remaining cells empty.

In Sheet3 cell B2 enter
=IF(Sheet2!B2="",MAX(B$1:B1)+1,"")
and copy B2 into all of B2:J106. For each column, Sheet3 aligns with Sheet2. It tags the rows of the untaught courses and numbers them sequentially.

In Sheet4 cell B2 enter
=IF(ROW()MAX(Sheet3!B:B)+1,"",
OFFSET(Sheet2!$A$1,MATCH(ROW()-1,Sheet3!B:B,0)-1,0))
and copy B2 into all of B2:J106. For each column, this formula matches the row number of Sheet4 with the sequential number of Sheet3 and returns the corresponding course name from Sheet2. The result is a contiguous course list for the dropdown.

Next we need to define a name. Use
Insert Name Define
For the name, use the letter v.
For "Refers to" use
=OFFSET(Sheet4!$A$2,
0,
COLUMN()-1,
MAX(OFFSET(Sheet3!$A$2,0,COLUMN()-1,105,1)),
1)
all on one line.

Finally, in Sheet1 select B2:J10 and use
Data Validation Settings
Under "Allow" choose "List".
For "Source" enter
=v
Check the box for "In-cell dropdown".
Fill in the "Input Message" and "Error Alert" tabs if desired.
Click "OK".
Each teacher now has a column of nine dropdown lists to pick up to nine untaught courses.

When teachers or courses are added or deleted in the future, add or remove rows/columns in all four sheets at once by multi-selecting the sheet tabs.

Hope this helps.

Thanks So Much! My apologies for not replying sooner as I have been out of office. I will try this in the morning and let you know.

Thank You!

WORKED FIRST TRY!

Thanks so much! I'll definitely be able to use this. Perhaps I can send you the finished scheduling workbook when I'm done (if you'd like). It's archaic, but I think it'll work for what we need.

Thank You Again, your assistance is very much appreciated.