| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
How to use a variable for a range
What I am trying to do is lookup data in the 2 column of a different
workbook. This data is organized as follows: John Smith Day spot trial ... Fri 7/1 Sat 7/2 Sun 7/3 John Doe Day spot trial ... Fri 7/1 Sat 7/2 Sun 7/3 What I have to do first is find the name ( ex: John Smith), and then find the correct day in his section of the data, and return the value. I've found and got the row information for both the name, and the date cells. ex: A7 and A33. What I am having a hard time doing is using these values as the range values in either a index, or vlookup statement. i.e. cell A34 = 7 - row where the name was found Cell A54 = 33 - row where the date was found. What I want to do is something along the order of this: INDEX(SPREADSHEET2!A34:F54,MATCH(NAMECELL,SPREADSH EET2!A34-A54),2) Where the 34'S and 54'S are variables. Is this even possible? |
|
#2
|
|||
|
|||
|
You could try the INDIRECT function,
INDIRECT("SPREADSHEET2!A"&A1&":F"&A2) where cell A1 and A2 contain the beginning and ending row numbers. Or, if these are the result of other functions you are using to find the row numbers, you could replace A1 and A2 with your functions and just use one large formula. "Jeff Lowenstein" wrote: What I am trying to do is lookup data in the 2 column of a different workbook. This data is organized as follows: John Smith Day spot trial ... Fri 7/1 Sat 7/2 Sun 7/3 John Doe Day spot trial ... Fri 7/1 Sat 7/2 Sun 7/3 What I have to do first is find the name ( ex: John Smith), and then find the correct day in his section of the data, and return the value. I've found and got the row information for both the name, and the date cells. ex: A7 and A33. What I am having a hard time doing is using these values as the range values in either a index, or vlookup statement. i.e. cell A34 = 7 - row where the name was found Cell A54 = 33 - row where the date was found. What I want to do is something along the order of this: INDEX(SPREADSHEET2!A34:F54,MATCH(NAMECELL,SPREADSH EET2!A34-A54),2) Where the 34'S and 54'S are variables. Is this even possible? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Variable Input Range for Combo Box | Excel Worksheet Functions | |||
| How can I assign a range starting cell based on a variable locati. | Excel Discussion (Misc queries) | |||
| How can I assign a range starting cell based on a variable locati. | Excel Worksheet Functions | |||
| empty variable range | Excel Discussion (Misc queries) | |||
| variable range countif | Excel Worksheet Functions | |||
Linear Mode
