Using Drop-Down list with offset

**rzacharia21** · 11-20-2012, 04:48 PM

Hey to you all,

Thank you in advance and I appreciate the help. I am attempting to create an NBA model that pins one team against another. For the opponent, I am using a drop down list that has the 29 other teams listed and I want to be able to pull the rosters of each respective team once I choose that team on the drop down list (the rosters are listed on another worksheet in the workbook). I have been able to execute the roster change by using numerical values and the offset function, but I want to be able to use the drop down list instead of having the user change the number and then see the roster change. I have attached my model for better understanding, as I know I am probably confusing. Thanks.

**JieJenn** · 11-20-2012, 04:51 PM

like this?

**JieJenn** · 11-20-2012, 04:53 PM

Messed up on the range. The correct formula should be

=INDEX('NBA '!$A$1:$AD$16,ROW()-16,MATCH(Opponent,'NBA '!$A$1:$AD$1,0))

**rzacharia21** · 11-20-2012, 08:37 PM

you are awesome thank you so much!!!!!! i know this next question may be one with a great amount of difficulty and may not even be possible, but is there a way to match the color scheme from the NBA tab to change dynamically when different teams are chosen from the drop down list?

**JieJenn** · 11-21-2012, 12:25 PM

You need VBA for that

**rzacharia21** · 11-21-2012, 03:19 PM

thank you again. is there anyway the border colors match the defined ones in the next worksheet? i'm not too familiar with VBA so it makes it kind of useless for me to tinker around in there and mess what you defined. another issue i have is that when i have a team with 14 players instead of 15, the drop down data validation list adjacent to the now blank 15th cell still shows up. is there a way to conditional format that cell (which is b35 in the copy of the model i attached). thank you again in advance, i really appreciate it.

**JieJenn** · 11-21-2012, 04:17 PM

You just need to extend your formula. I fixed the other way.

**JieJenn** · 11-21-2012, 04:18 PM

What happen to Chicago bulls?

**rzacharia21** · 11-21-2012, 04:45 PM

what do you mean what happened to the chicago bulls?

**rzacharia21** · 11-21-2012, 04:48 PM

the most recent model I attached is the one that is correct. I just want to figure out how to use VBA to extract the border colors from the team name cells in the NBA tab and how to somehow blank the last drop down option (in B35) when there is nothing in A35.

**rzacharia21** · 11-23-2012, 04:39 PM

does anyone know how to format row 35 so that if A35 is blank, the data validation entry in B35 will also be blank? also, does anyone know to use VBA so that the border color for cell A19 on the Chicago Bulls tab follows the border color of the respective team listed on the NBA tab? Thank you very much.