| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Return a value from a list in another worksheet
Hello, I manage a sports league, and one of my most common tasks is to make the season schedule at the beginning of the year, where each time has to play another, at different times of the week. Each team has sent me a set of availability restrictions, days× of the week that they are unable to play. Thus, when I make my schedule, I have to take these restrictions into account. I have one separate worksheet for the game *schedule*, and another for the *restrictions*. Thus in the worksheet "schedule" I have the home team under column A, playing versus the away team on column B. In the worksheet "restrictions", I have the team name under column A, and their restrictions under column B. e.g. _\"Schedule\"_worksheet_ -Column A / Column B- Footsies / MISN Impossible Money Shot / Rejects Grrr / McGrads I Pink Seagulls / Mailmen FC etc. _\"Restrictions\"_worksheet_ -Column A / Column B- Footsies / no Jan 9,12,16 no Feb 6-8 Money Shot / No Jan 9, No Thursdays Grrr / no Jan 16 etc. My question: is there a way in Excel to set up columns C & D in the worksheet "schedule" such that it returns the restrictions I have set up in the worksheet "restrictions"? (column C for the home team, and column D for the away team). I basically need a function that goes through the list in A, matches the team name in the restrictions worksheet, and then returns the restriction "value" (which is in fact, text) in the "schedule" worksheet. I tried the "IF" function, but it doesn't work. Any help would be appreciated. -- mpanty ------------------------------------------------------------------------ mpanty's Profile: http://www.excelforum.com/member.php...o&userid=21377 View this thread: http://www.excelforum.com/showthread...hreadid=501194 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Return a value from a list in another worksheet
No clues? Seems pretty straightforward in theory... I just don't have sufficient Excel knowledge to know of the right formula... -- mpanty ------------------------------------------------------------------------ mpanty's Profile: http://www.excelforum.com/member.php...o&userid=21377 View this thread: http://www.excelforum.com/showthread...hreadid=501194 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Return a value from a list in another worksheet
use vlookup to look for the team name in the other workshheet and return the
restriction in cols c for col a and d for colb the lookup formula say in col c1 of sheet "schedule" looks like this =vlookup(A1,restrictions!A1:B7,false) for this to work the team names(spelling wise) have to be identical in both sheets -- paul remove nospam for email addy! "mpanty" wrote: Hello, I manage a sports league, and one of my most common tasks is to make the season schedule at the beginning of the year, where each time has to play another, at different times of the week. Each team has sent me a set of availability restrictions, days× of the week that they are unable to play. Thus, when I make my schedule, I have to take these restrictions into account. I have one separate worksheet for the game *schedule*, and another for the *restrictions*. Thus in the worksheet "schedule" I have the home team under column A, playing versus the away team on column B. In the worksheet "restrictions", I have the team name under column A, and their restrictions under column B. e.g. _\"Schedule\"_worksheet_ -Column A / Column B- Footsies / MISN Impossible Money Shot / Rejects Grrr / McGrads I Pink Seagulls / Mailmen FC etc. _\"Restrictions\"_worksheet_ -Column A / Column B- Footsies / no Jan 9,12,16 no Feb 6-8 Money Shot / No Jan 9, No Thursdays Grrr / no Jan 16 etc. My question: is there a way in Excel to set up columns C & D in the worksheet "schedule" such that it returns the restrictions I have set up in the worksheet "restrictions"? (column C for the home team, and column D for the away team). I basically need a function that goes through the list in A, matches the team name in the restrictions worksheet, and then returns the restriction "value" (which is in fact, text) in the "schedule" worksheet. I tried the "IF" function, but it doesn't work. Any help would be appreciated. -- mpanty ------------------------------------------------------------------------ mpanty's Profile: http://www.excelforum.com/member.php...o&userid=21377 View this thread: http://www.excelforum.com/showthread...hreadid=501194 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Return a value from a list in another worksheet
what no response??? :-)
-- paul remove nospam for email addy! "paul" wrote: use vlookup to look for the team name in the other workshheet and return the restriction in cols c for col a and d for colb the lookup formula say in col c1 of sheet "schedule" looks like this =vlookup(A1,restrictions!A1:B7,false) for this to work the team names(spelling wise) have to be identical in both sheets -- paul remove nospam for email addy! "mpanty" wrote: Hello, I manage a sports league, and one of my most common tasks is to make the season schedule at the beginning of the year, where each time has to play another, at different times of the week. Each team has sent me a set of availability restrictions, days× of the week that they are unable to play. Thus, when I make my schedule, I have to take these restrictions into account. I have one separate worksheet for the game *schedule*, and another for the *restrictions*. Thus in the worksheet "schedule" I have the home team under column A, playing versus the away team on column B. In the worksheet "restrictions", I have the team name under column A, and their restrictions under column B. e.g. _\"Schedule\"_worksheet_ -Column A / Column B- Footsies / MISN Impossible Money Shot / Rejects Grrr / McGrads I Pink Seagulls / Mailmen FC etc. _\"Restrictions\"_worksheet_ -Column A / Column B- Footsies / no Jan 9,12,16 no Feb 6-8 Money Shot / No Jan 9, No Thursdays Grrr / no Jan 16 etc. My question: is there a way in Excel to set up columns C & D in the worksheet "schedule" such that it returns the restrictions I have set up in the worksheet "restrictions"? (column C for the home team, and column D for the away team). I basically need a function that goes through the list in A, matches the team name in the restrictions worksheet, and then returns the restriction "value" (which is in fact, text) in the "schedule" worksheet. I tried the "IF" function, but it doesn't work. Any help would be appreciated. -- mpanty ------------------------------------------------------------------------ mpanty's Profile: http://www.excelforum.com/member.php...o&userid=21377 View this thread: http://www.excelforum.com/showthread...hreadid=501194 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Return a value from a list in another worksheet
Thank you very much Paul! :) Worked like a charm! One thing you forgot however :P was to specify the "Col_index_num". The function is in fact with the format VLOOKUP(lookup_value,table_array,col_index_num,ran ge_lookup) where Col_index_num is the column number in table_array from which the matching value must be returned. A col_index_num of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on. In my case, I needed "2". :) But thanks again! :D -- mpanty ------------------------------------------------------------------------ mpanty's Profile: http://www.excelforum.com/member.php...o&userid=21377 View this thread: http://www.excelforum.com/showthread...hreadid=501194 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Return a value from a list in another worksheet
oops youre right,but glad you sorted it out....
-- paul remove nospam for email addy! "mpanty" wrote: Thank you very much Paul! :) Worked like a charm! One thing you forgot however :P was to specify the "Col_index_num". The function is in fact with the format VLOOKUP(lookup_value,table_array,col_index_num,ran ge_lookup) where Col_index_num is the column number in table_array from which the matching value must be returned. A col_index_num of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on. In my case, I needed "2". :) But thanks again! :D -- mpanty ------------------------------------------------------------------------ mpanty's Profile: http://www.excelforum.com/member.php...o&userid=21377 View this thread: http://www.excelforum.com/showthread...hreadid=501194 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| list worksheet macros/VBA without adding worksheet | Excel Discussion (Misc queries) | |||
| Return list of uncleared checks | Excel Worksheet Functions | |||
| form on one worksheet and the list on different worksheet | Excel Worksheet Functions | |||
| Selecting items from a list. To transfer to another worksheet? | Excel Discussion (Misc queries) | |||
| create a list of worksheet names (from a single folder, or open files) | Excel Discussion (Misc queries) | |||
Hybrid Mode
