Prev: how can I use conditional formatting with formula and 3 poss resul
Next: Pivot Table Wizard causes crash
From: Art on 10 Mar 2010 16:50
I have a sheet in Excel 2007 that lists all of the courses we offer. Columns
L to O indicate if a particular course if offered each quarter.

For example, for one course (row 5), you might see

L5: NO
M5: 5F
N5: 1F1B
O5: NO

L5 (Summer 2010) and O5 (Spring 2011) equal "NO" because the course is not
offered that quarter. M5 (Fall 2010) equals 5F because 5 sections of
F(ace-to-face) sections are offered, and N5 equals 1F1B because 1 section of
F(ace-to-face) and 1 section of B(lackboard, or online) is offered.

I am creating a separate sheet that users can use to lookup a list of
courses that meet certain criteria. An easy example, they can choose a
program (business, culinary, etc.) and the spreadsheet lists all of the
courses in that particular program.

I want users to be able to select a term (e.g., SU2010) then display all of
the courses offered that quarter---that is, the cell in column L5 is NOT
blank. However, I am not sure how to handle this.

If they select a program from a drop-down box, the spreadsheet searches the
list of courses and display the courses with that program listed in a
particular column.

For terms, they are searching for a term (e.g., SU2010), but there aren't
any cells in any row that would have that as the contents of the cell. The
cell in a certain column (in this case, Column L), is blank or not blank.

Logically, it would seem like if a user chooses SU2010, it could look in a
corresponding column (it would somehow know that SU2010 is Column L), then
look for courses where the cell in the column is NOT blank. But, there could
be many choices for terms, and just knowing the term doesn't connect
correctly to how the information is stored in the spreadsheet.

Any thoughts how to carry out this lookup function? THANKS!!!
From: Mayasmom on 10 Mar 2010 19:24
You can use an index/match formula.

"Art" wrote:

> I have a sheet in Excel 2007 that lists all of the courses we offer. Columns
> L to O indicate if a particular course if offered each quarter.
>
> For example, for one course (row 5), you might see
>
> L5: NO
> M5: 5F
> N5: 1F1B
> O5: NO
>
> L5 (Summer 2010) and O5 (Spring 2011) equal "NO" because the course is not
> offered that quarter. M5 (Fall 2010) equals 5F because 5 sections of
> F(ace-to-face) sections are offered, and N5 equals 1F1B because 1 section of
> F(ace-to-face) and 1 section of B(lackboard, or online) is offered.
>
> I am creating a separate sheet that users can use to lookup a list of
> courses that meet certain criteria. An easy example, they can choose a
> program (business, culinary, etc.) and the spreadsheet lists all of the
> courses in that particular program.
>
> I want users to be able to select a term (e.g., SU2010) then display all of
> the courses offered that quarter---that is, the cell in column L5 is NOT
> blank. However, I am not sure how to handle this.
>
> If they select a program from a drop-down box, the spreadsheet searches the
> list of courses and display the courses with that program listed in a
> particular column.
>
> For terms, they are searching for a term (e.g., SU2010), but there aren't
> any cells in any row that would have that as the contents of the cell. The
> cell in a certain column (in this case, Column L), is blank or not blank.
>
> Logically, it would seem like if a user chooses SU2010, it could look in a
> corresponding column (it would somehow know that SU2010 is Column L), then
> look for courses where the cell in the column is NOT blank. But, there could
> be many choices for terms, and just knowing the term doesn't connect
> correctly to how the information is stored in the spreadsheet.
>
> Any thoughts how to carry out this lookup function? THANKS!!!
 | 
Pages: 1
Prev: how can I use conditional formatting with formula and 3 poss resul
Next: Pivot Table Wizard causes crash