Creating formula which auto looks up specific data & makes calculation based on that data

I believe this can be achieved with some VBA programming, I'll try to post an example later.

Ok, this is a very rough approach as I have just started VBA basics. The list on the left is the team data with dates, wins, losses etc.

On the right there is a drop-down list of all teams. Select a team and click the Process button. There is a macro assigned to the button that does the calculations of total wins, losses and draws for that team. The coding is very basic and unprofessional, but I suppose it is a good start and besides its my first working macro, created from scratch

Thank you very much for the help; it certainly seems like VBA programming is the way to go for this having looked at your example.

I guess I'd better start learning VBA basics!

So am I right in thinking that my best bet will be to use VBA programming to have a third sheet where all of the percentages for each team (four for each team: Overall Win %, Win % At Home, Win % Away, Win % In Last 5 games) are calculated by the click of a button, then have my Home/Draw/Away predictions on my original sheet, and have these calculated by using formulae that reference the sheet where the VBA programming will be?

If I had all of that set up as above, would I be able to set the spreadsheet up so that if I typed 'Arsenal' in the home team slot and 'Burnley' in the away team slot, the sheet would automatically look up the VBA programming's % values for each team and calculate the odds I want automatically?

Thanks for any more help you can give. Appreciate it!

Okay, new help needed if possible to anyone reading!!

I've written up a VBA code specifically for my spreadsheet, based on your VBA code above bmouse. I've written down the columns and values correctly, but for some reason Excel is telling me there are errors in this code. The errors seem to be wherever a line uses the Cells line. So, for example, there is an error in this line "If Cells(2, “A") = Cells(StartNumber, “K") Then".

If you or anyone else good with VBA could take a look at this and see where I'm going wrong I'd really appreciate it! Thanks

----------

Dim counterW As Integer
Dim counterL As Integer
Dim counterD As Integer
Dim StartNumber As Integer
Dim EndNumber As Integer

EndNumber = 1000
counterW = 0
counterL = 0
counterD = 0

For StartNumber = 2 To EndNumber

If Cells(2, “A") = Cells(StartNumber, “J") Then

If Cells(StartNumber, “N") = "H" Then

counterW = counterW + 1

Else

If Cells(StartNumber, “N") = "D" Then

counterD = counterD + 1

Else: counterL = counterL + 1
End If
End If
End If

Next StartNumber




For StartNumber = 2 To EndNumber

If Cells(2, “A") = Cells(StartNumber, “K") Then

If Cells(StartNumber, “N") = "A" Then

counterW = counterW + 1

Else

If Cells(StartNumber, “N") = "D" Then

counterD = counterD + 1

Else: counterL = counterL + 1


End If
End If
End If

Next StartNumber

Cells(2, “C").Value = counterW
Cells(2, “D").Value = counterD
Cells(2, “E").Value = counterL

It looks solid, no visible mistakes, except there are 2 lines missing - 1st and last. The 1st line is Sub SubName () and the last line is End Sub, did you forget those or simply did not add to the post?

If you want me to fiddle with it, you can post the entire book with the code, I'll see if I can get it working.

That would be brilliant if you don't mind taking a look! Thank you so much for the help bmouse, appreciate it!

The file is with this post.

The sheet you want is the third sheet along, entitled 'E0'. The data is in a table to the right, and the macro button is to the left, just below where the data should end up if the macro is working.

There are lots of other sheets but you don't need to worry about those! Should also note that in the relevant 'E0' sheet, each fixture has a lot more data with it than specified above, but the only relevant columns are J, K and N (Home Team, Away Team and Full Time Result).

Thanks very much

After your 1st FOR command you use the RANGE parameter, and there is your problem. Re-write that line of code with the CELLS parameter as it is everywhere else and it should work.

Oops. Changed that and corresponding code and it's working now, awesome.

Thanks so much for your help.

Final question: If I wanted all 20 teams listed out, and to be able to update all of their Win Records with the click of a button, am I right in thinking that I could do that with a single button by writing multiple Subroutines in that one Macro, altered slightly for each row the teams would be listed on?

Thanks; also, I'm new to this forum, so if there's a common way to 'upvote' you or say thanks for being a big help that you'd appreciate let me know!

I suppose you can write additional subroutines to do all teams with a single button press, perhaps not the most efficient way, but I believe if it works for 1 team, it should work for 20 as well.

If you wish to thank people you can click Add reputation under their name, left side of any post. Also if you think the problem is solved, please mark the thread SOLVED, I think its on the top of the thread, right hand side.

Glad I could help you out, and good luck