If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
vlookup---using data outside the range specified in the formula
I am sure I can guess the logical answer to this question, 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.) |
#2
|
|||
|
|||
vlookup---using data outside the range specified in the formula
Sorry I posted this question twice. I got a "service temporarily not
available" message, so I waited and reposted, thinking the first question didn't get posted. "Art" wrote: I am sure I can guess the logical answer to this question, 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.) |
#3
|
|||
|
|||
vlookup---using data outside the range specified in the formul
Here's my response in your earlier thread:
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 -- Max Singapore --- |
#4
|
|||
|
|||
vlookup---using data outside the range specified in the formul
That was a ridiculously simple solution! Thank you so much...definitely a
tool that I will take advantage MANY times!!! "Max" wrote: Here's my response in your earlier thread: 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 -- Max Singapore --- |
#5
|
|||
|
|||
vlookup---using data outside the range specified in the formul
Welcome, do take a moment to hit YES in that earlier response
-- Max Singapore --- "Art" wrote: That was a ridiculously simple solution! Thank you so much...definitely a tool that I will take advantage MANY times!!! |
Thread Tools | |
Display Modes | |
|
|