using vlookup to find data outside a specified range

[Art]

I am sure I can guess the logical answer to this questions, but...

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!!!

 

[Max]

Addressing this line:

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?

Extend your horizon beyond vlookup, use index/match. Its much more
versatile, you can match on any col and "directly" return any other col to
the left or right of the match col, and accomplish this w/o having to fuss
around with col index numbers to boot.

Eg instead of : =VLOOKUP(B5,Sheet1!$B$5:$J$397,2,0)
Try this: =INDEX(Sheet1!C:C,MATCH($B5,Sheet1!$B:$B,0))
to return the same results as the vlookup

Just change the index bit: INDEX(Sheet1!C:C
to: INDEX(Sheet1!A:A
if you want to return the results from col A
(instead of col C)

Enjoy the breakthrough? wave it, hit YES below