Sports Team Win/Loss/Draw/Upcoming spreadsheet

Hi all!

I am trying to set up a spreadsheet which shows a teams last 5 results

For example Roosters - Last 5 results are

Round 23 - "Upcoming"
Round 22 - "Win"
Round 21 - "Win"
Round 20 - "Win"
Round 19 - "Bye"




Ive got all the scores and a margin, but ive tried countifs/sumifs/index etc to get it done - I think the part I am struggling with is how to know if the team had a BYE for that round ?

I have attached the file below - Any suggestions would be great!

Hi Exodus_NZ

I hope the attached does what you want.

I could not see from your example how to identify a "Bye", so I have ignored it Is it, perhaps that the score id 40:0 ? How should the Bye be shown for (in your example) Round 19 Sharks?

To run the code, press Ctrl+shift+Q
To see the code press Alt+F8 and select "step into"

You need to ensure that all teams are listed in column J

Let me know if it is what you want.

Regards
Alastair

Hey mate

Thanks alot!!

That is amazing! Ive been trying for a few weeks and couldnt get anything going using the commands I commented above.

A bye is when the team doesn't exist, however it seems that in your case the cell is left blank, so its kind of obvoius it was a bye?

Just saw your code (ive never worked with macro's before), could possibly add


Something like "Case Is is null"

Thanks again, look forward to your reply

P.S - I dont see a like button etc so couldnt click it for you!

Hi Exodus_NZ

Part of the reason that VBA had to be used is that you have 2 columns (Home and Away) going into 1.

Your "Case Is is null" will not work as, once it has put something in (in this case "null"), the VBA moves on, so will not record a later proper result. What I have done is to initially make the result "Bye" and then overtype if there is a result.

Don't worry about stars - I don't!

Regards
Alastair

It was Sharks vs Roosters

But they got a hiding (Lost 40-0)!!