# Solved Drop down NFL Teams and VLookup?

February 26, 2017 at 16:53:22
Specs: Windows 10
 Greetings. I currently have 2 drop down lists with all 32 NFL teams. One labeled Visiting team, and the other home team. Here is what I need to happen: When I select, say, Arizona, I need to pull certain rushing and passing stats into this sheet so as to make a prediction on the game outcome. I've been told VLookup will do this, but cannot put it together in my head. Thank you in advance for your help with this!

See More: Drop down NFL Teams and VLookup?

March 1, 2017 at 09:59:37

#1
February 26, 2017 at 18:16:43
 Without knowing how your sheet is laid out, it's hard to offer anything more than a generic suggestion. For example, let's say your sheet looks like this, with your drop down in A2 and your stats table in E1:G3. (Obviously it will be much longer and wider)``` A B C D E F G 1 Team Rushing Passing Team Rushing Passing 2 Buffalo Arizona 300 200 3 Buffalo 400 450```Now let's say you want to pull the Rushing and Passing stats for your A2 choice into B2 & C2.Try these formulas:B2: =VLOOKUP(A2,\$E\$2:\$G\$3,2,0) (Should return 400)C2: =VLOOKUP(A2,\$E\$2:\$G\$3,3,0) (Should return 450)I don't know if you are familiar with how VLOOKUP works, so I'll save the explanation unless you ask. I'm more than willing to explain it, but I don't want to waste my time or yours if the explanation is not needed. :-)If your spreadsheet is much more complicated than I'm guessing, you'll need to explain the setup and explain why you are having trouble with VLOOKUP.

Report •

#2
February 26, 2017 at 20:20:29
 Thank you for your help with this. I'll try to lay this out in the format that I currently have; Visiting Team Home Team (Drop Down Box) (Drop Down Box) Visitor Home Home Vistor Offense Defense Offense Defense Rush Att's (Cell) (Cell) (Cell) (Cell)Rush Yds (Cell) (Cell) (Cell) (Cell)This is the format for rushing figures. Right below this would be the same format only for passing stats. So, What I'm trying to accomplish is when a certain pair of teams is selected from the drop down list, those stats for those particular teams need to populate the cells pictured so a score prediction can be made. Let me know if this helps, if not, I would be willing to take a screen snap shot if you let me know how to accomplish this in windows 10. And thanks again for your interest in my program.

Report •

#3
February 27, 2017 at 05:49:33

Report •

Related Solutions

#4
February 27, 2017 at 06:52:55
 ```Visiting Team Home Team ```

Report •

#5
February 27, 2017 at 07:05:52
 You might want to try that again. All I see is one line. That's not a lot for us to work with.

Report •

#6
February 27, 2017 at 13:04:52
 Sorry about that, am having a problem getting the picture to you as I don't see a way to post it to you on here.

Report •

#7
February 27, 2017 at 13:21:52
 Why don't you do what I suggested in response # 3?

Report •

#8
February 27, 2017 at 19:13:51
 I tried that, but didn't know where to post the link to the picture so you could see it. It's already on zippyshare, but just need to know how to post the link as the blue link information appears vague. Sorry.

Report •

#9
February 27, 2017 at 19:59:34
 Just copy the link from zippyshare and paste it into a post.

Report •

#10
February 27, 2017 at 20:29:05
 Duh.....sorry about that. I'm not normally this stupid I promise....Here you go. http://www101.zippyshare.com/v/ZWWh...This the sheet where the predictions will take place. Pulling data from another sheet called "Main". If you need to see this sheet as well, I would be glad to post a link to this one as well. Thanks for all your help as well..

Report •

#11
February 27, 2017 at 21:04:25
 There is not much that I can do with just that image. I need to know where the data is coming from and how it is laid out.

Report •

#12
February 28, 2017 at 06:10:59
 I would also like to know if you understand how the VLOOKUP function works. At this point, I'm not saying that VLOOKUP is the method you should use. I won't know that until I see how your source data is laid out, but assuming that VLOOKUP will work, I would like to know how much of an explanation I should provide to make sure that you can adapt it to your needs.

Report •

#13
February 28, 2017 at 06:51:00
 I know a little bit about it from following some instructional videos, and I think I get the concept of how it works, but just can't seem to understand how to program the same cells to pull data over from a different worksheet for all 32 teams. That's where the problem rests for me.

Report •

#14
February 28, 2017 at 06:55:27
 Are you going to post a link to the source data? I can't do anything until I know how the source data is laid out. I've asked for the layout of the source numerous times.Until you provide that information, there is nothing else that I can do.

Report •

#15
February 28, 2017 at 07:06:32
 My Apologies as I didn't see the second request. Anyway, here is the picture of what the sheet looks like for Arizona. There are 31 more laid out just like this with data needing to be pulled over from the right side of the sheet before the 2016 averages as those will be 2017 season to date averages as the scores and stats get entered from pro-football-reference.com.http://www82.zippyshare.com/v/Dy4I3...

Report •

#16
February 28, 2017 at 07:53:10
 re: " Anyway, here is the picture of what the sheet looks like for Arizona. Your image does not include any Column letters or Row numbers. Without that information the best I can offer is a generic solution. I would rather not make assumptions and guesses as to the complete layout of your workbook, so it would be best if you did one of 2 things: Either repost both of your images and include the Column letters and Row numbers or upload the entire file to zippyshare and post a link here.I would prefer to have access to the entire file so that I do not have to recreate the file from an image. With the actual workbok in front of me, I can test various options. If you do decide to post the entire file, please put some example source data in the team tables. i.e. Make believe that it is the 3rd week of the season and that you already have data for weeks 1 & 2. That way we have some actual data to pull into the destination table.re: "There are 31 more laid out just like this""I don't know what that means. 31 more tables on a single sheet or 31 more sheets with a single team's table on each sheet?That is another reason that I would prefer that you upload the entire workbook to zippyshare. I really want to see exactly what you have, what formulas you are using, what cells are merged, etc. If you want to condense it down to just 3 or 4 teams, that's fine. As long as I have enough source data to work with, I should be able to come up with a solution. At this point I'm still flying somewhat blind.

Report •

#17
February 28, 2017 at 20:15:18
 Well, it sounds like I should have done this all along, but being as frustrating as this whole project has been for the past 2 years, I had all but given up. Anyway here is the entire workbook that I have been toying with. And I'm now of the opinion that if you can't fix this, nobody can apparently.http://www52.zippyshare.com/v/GjIDd...Again, sorry to make this so frustrating, and thank you for your help...

Report •

#18
February 28, 2017 at 20:25:48
 I'll work on it tomorrow. Based on your screen shots I did play around with a few things and may have some ideas. In the meantime, if you are not familiar with Named Ranges, your should study up on them. I think that using Named Ranges will make a solution fairly simple. If we name each team's stat table with the name of the team, we can then use the name from the drop down to reference the range for that team. We'll see.

Report •

#19
February 28, 2017 at 20:35:04
 Hey, if it sounds good to you, then so be it. I'm open for just about anything that sounds like a solution after all this time... thanks again...

Report •

#20
March 1, 2017 at 09:59:37

Report •

#21
March 1, 2017 at 13:19:22
 Outstanding! Will look at this closer when I get home from work tonight. And thank you once again for your efforts with this as I will definitely try what you have suggested.

Report •

#22
March 3, 2017 at 13:35:36
 DerbyDay. I followed your instructions to the letter, and it works like a charm! I can't thank you enough for all that you did on this, as this has been an on-going project for over 2 years now. But most of all, thank you for being such a good teacher putting the instructions into words that people such as myself can understand and apply. You are a truly gifted teacher, and thank you once again....Steve

Report •

#23
March 3, 2017 at 16:24:32
 I'm glad you got it working. I do have a question for you:One of the stats you have the Analyzer sheets is "Rushing Attempts Allowed". That is not a stat I am familiar with. Is that nothing more than the avg Rushing Attempts of that week's opponent?

Report •

#24
March 3, 2017 at 19:40:37
 With each team playing each other differently as the weeks go by, that stat tells me how many rushing attempts their defense is allowing on a week to week basis, along with an average as well. Probably doesn't do anything for figuring point differentials, just shows the user how many times the other team ran the ball against their defense as opposed to passing.

Report •

#25
March 4, 2017 at 05:11:46
 That's what I thought. So it's more of a rushing attempts against stat vs. allowed.That might tell you something about their run defense, i.e. whether or not teams like to run against them. Thanks.

Report •

#26
March 4, 2017 at 08:56:10
 Correct. Sort of a visual thing for the users that see things that way. Same way with the charts, strictly visual. I did come across another question if you're up for it.Some time ago, I was toying with the idea of trying to make the "Main" sheet more automated as far as how it gets it's data from say, another website. i.e. pro-football-reference.com. Is it possible to ask the program to only download certain stats, or does it have to get an entire section with formula's sorting out the data?

Report •

#27
March 6, 2017 at 07:44:12
 I am not very familiar with downloading data from a website into Excel. It's just not something I need to do very often. When I do, I typically copy/paste and the move stuff around, or download a csv files and massage the data.However, it doesn't necessarily have to be done with formulas. It's possible that a Macro might be more efficient - and less risky. A macro could pull actual values into the cellseliminating the danger of a formula being changed or deleted.Before I could offer any advice, I would need to know your current process and have access to the workbook that uses the formulas.

Report •

#28
March 6, 2017 at 12:17:28
 Thank you for the input, as it was an idea I was just toying with. It does look and sound like a lot of work though. I think I'm going to "Massage" what I already have thanks to the awesome help that you have already provided with the named ranges. I can't begin to tell you how helpful that has been as I am now able to move forward with the rest of this project.

Report •

#29
March 10, 2017 at 09:11:15
 DerbyDad, I came across another situation with my project needing help on one of the formulas. I'm hoping that you still have the spreadsheet that I sent you? If you don't I can send it again when I get home from work..Steve

Report •

#30
March 10, 2017 at 10:00:13
 What is the issue?

Report •

#31
March 10, 2017 at 11:47:23
 Well, on the "Main" sheet, on the right side where the season-to-date stats are, you'll see a couple of rows, one named ATS w/l/p, something like that, the other one is labeled O/U w/l/p, those formulas in those cells are for all of the games a team will play. What I am trying to do is break the countif formula to show home games in that first column, then the away games in the column just to the right of that which I have set up. Is this even doable?

Report •

#32
March 10, 2017 at 13:17:58

Report •

#33
March 10, 2017 at 13:33:58
 Looks good, what do I use for when the team doesn't cover the spread or over/under for that game? Is that what the <> @ part of the formula is for? Did you also see the existing formula that is there now? Does that make sense?

Report •

#34
March 10, 2017 at 14:32:10