A

#### Art

I have a sheet (Sheet 1) in Excel 2007 with a list of courses. The Course ID

is in Column B and course title in Column C. The remaining columns are used

to keep track of content that is developed for each of the 10 weeks of the

course. I use a COUNT formula to show how many cells in the "development week

columns" are NOT blank. If no content, for example is developed for BUS 110

(thus, no dates of completion are includes in Columns X, Y, Z, etc.), a 0 is

put in column A. If we completed three weeks, we put completion dates in

cells X, Y, and Z, so the formula in Column A puts a 3 (there are three

non-blanks in the range). The formula in, A1, for example is

=COUNTA(X9:AG9)

A :: B :: C

0 :: BUS 110 :: Theory of the Firm

3 :: BUS 140 :: Sales and Sales Management

6 :: BUS 312 :: Labor Relations

2 :: BUS 455 :: Finance

Here's the problem I'm trying to figure out.

On Sheet 2, I want to have a summary of each course. The user enters a

Course ID, and the spreadsheet autopopulates information about the course

from Sheet 1. So, to do this, I use Course ID (which is B5 in the sheet) as

the search cell in VLOOKUP.

For example, it autopopulates the Course Title box using the following

VLOOKUP:

=VLOOKUP(B5,Sheet1!$B$5:$J$397,2,0)

Since the user "searches" by Course ID, I have to have the column B be the

left-most column in the VLOOKUP; however, in doing so, I don't have "access"

to the contents of the cells in Column A when I want to autopopulate the

"Progress" cell on Sheet 2.

Is there a way to still access the data in Column A in Sheet 1, even though

VLOOKUP uses the range starting with Column B in Sheet 1?

If I can't get access to that cell, is there a way to use COUNT formula

similar to the one included above to count the occurrences of non-blank cells

in a series of columns in the respective row that corresponds to the Course

ID it looked up? (So, for example, if it looks up BUS 312, this is row 3, so

the COUNT formula on Sheet 2 in the "Progress" cell would just count the

occurrences of non-blanks in row 3, in the same series of columns.)

Thanks!!!