Hi
When there is only one entry with course name "Effective Presentations", and
the column with course name is leftmost in table, and on another sheet you
entered the same course into some cell, then you can use VLOOKUP to populate
cells with rest of data for this course from main sheet - like this:
=VLOOKUP(A2,Main!$A$2:$X$100,4,0)
(the example formula returns the value from 4th column in search range,
where the value in first column matches exactly with value in cell A2. NB!
The first occurrence is found!).
When the key column (course name in your example) isn't leftmost, then with
rest of conditions same as above you have to combine index and match
functions - like this:
=INDEX(Main!$A$2:$A$100,MATCH(A2,Main!$D$2:$D$100,0),)
When there are many rows which match same condition (probably your case),
then you have 2 options:
a) On other sheets, you get data from main table using ODBC query [you must
have your main table, header row included, defined as non-dynamic named
range (p.e. CourseTable=Main!$A$1:$X$200) before]. And my advice is to use
single-word headers in main table. So on sheet CoursesTaken you create a
query (Data.GetExternalData from menu) with SQL string:
SELECT CourseTable.CourseName, CourseTable.Course.Time, ... FROM ´C:\My
Documents\CourseWorkbook´.CourseTable CourseTable WHERE
CourseTable.CourseName <> '' ORDER BY CourseTable.CourseTime,
CourseTable.CourseName;
You can set the query to be refreshed when workbook is opened, or refresh it
manually, or design an Activate event for worksheet, which refreshes the
query whenever you activate the sheet (but always the query returns data
saved last - any changes made on main sheet after last save are left out).
b) On main sheet, you add an additional column, where an identifier for
every row is calculated. P.e. in your example you add an empty column A
(other columns are shifted to left), and into cell A2 enter the formula:
=IF ($B2="";"";MATCH($D2,{"Courses taken";"Courses needed";"Courses placed
on hold";},0)+COUNTIF($D$2:$D2,$D2)/100)
which returns 1.01 for first "Courses taken" in table, 1.02 for second one,
...., 2.01 for first "Courses needed" and so on. Copy the formula down - you
can have it copied for more rows you have actual data in your table - for
future entries. I assumed that you had a CourseGroup column in your main
table on 3rd place.
P.e. on sheet CoursesTaken, when you want the data displayed starting from
row 2 (row 1 is header row), into A2 you enter the formula
=IF(ISERROR(VLOOKUP(1+(ROW()-1)/100,Main!$A$2:$Y$100,2,0)),"",VLOOKUP(1+(ROW
()-1)/100,Main!$A$2:$Y$100,2,0))
into B2 you enter the formula
=IF($A2="","",VLOOKUP(1+(ROW()-1)/100,Main!$A$2:$Y$100,3,0))
etc. (The CourseGroup column you drop of-course)
And then you copy all formulas from row 2 down for number of rows you think
reasonable.
A variation of this solution (I advice this, when there are many groups you
want your main table to divide), is, where you create a report sheet with
selectable group name (you can use validation list for this). On main sheet,
in additional column the value is calculated only for rows, where
CourseGroup matches with one selected on report sheet, i.e. the formula p.e.
for A2 will be something like this:
=IF($D2=Report!$B$1,,"COUNTIF($D$2:$D2,$D2)")
and on Report sheet, when you want data start from row 4 (row 3 is header),
the formula in A4 will be:
=IF(ISERROR(VLOOKUP(ROW()-3,Main!$A$2:$Y$100,2,0)),"",VLOOKUP(ROW()-3,Main!$
A$2:$Y$100,2,0))
etc.
Now, when you select different group in B1, all entries for selected group
are displayed on report sheet.