Announcement

Collapse
No announcement yet.

World Cup Fantasy Football

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • World Cup Fantasy Football



    Hi There, Here's a little programme I wrote for the upcoming World Cup
    I run this at my workplace and usually get about 80-100 people entering.

    Here's how it works:
    1. Ask everyone to make their selections based on the following criteria: Give 32 pts to the Country you think will win the world cup, 31 pts to the runner up, and so on down to the Country you consider the weakest on just 1 pt.
    2. Enter everybody's selections
    3. Enter the number of wins draws and goals after every game.
    4. Sort & share the results.

    That's all there is to it.

    How much you charge to enter and how you split the prize money is up to you.

    If anybody wants to add any improvements then they would be welcomed.

    All the best Gary Hunt
    Attached Files

  • #2
    Re: World Cup Fantasy Football

    Hi Gary

    Good initiative.

    I think you should make a few adjustments to the model. I think this formula;

    =SUM((Data!$C4*Results!$C$4)+(Data!$D4*Results!$D$4)+(Data!$E4*Results!$E$4)+(Data!$F4*Results!$F$4)+(Data!$G4*Results!$G$4)+(Data!$H4*Results!$H$4)+(Data!$I4*Results!$I$4)+(Data!$J4*Results!$J$4)+(Data!$K4*Results!$K$4)+(Data!$L4*Results!$L$4)+(Data!$M4*Results!$M$4)+(Data!$N4*Results!$N$4)+(Data!$O4*Results!$O$4)+(Data!$P4*Results!$P$4)+(Data!$Q4*Results!$Q$4)+(Data!$R4*Results!$R$4)+(Data!$S4*Results!$S$4)+(Data!$T4*Results!$T$4)+(Data!$U4*Results!$U$4)+(Data!$V4*Results!$V$4)+(Data!$W4*Results!$W$4)+(Data!$X4*Results!$X$4)+(Data!$Y4*Results!$Y$4)+(Data!$Z4*Results!$Z$4)+(Data!$AA4*Results!$AA$4)+(Data!$AB4*Results!$AB$4)+(Data!$AC4*Results!$AC$4)+(Data!$AD4*Results!$AD$4)+(Data!$AE4*Results!$AE$4)+(Data!$AF4*Results!$AF$4)+(Data!$AG4*Results!$AG$4)+(Data!$AH4*Results!$AH$4))*Scoring!$C$2

    Should be this;

    =SUMPRODUCT(Results!$C$4:$AH$4,Data!$C4:$AH4)*Scoring!$C$2

    I think you should ditch the Sort macro and put a worksheet activate macro which does the same thing that looks like this.

    Code:
    Private Sub Worksheet_Activate()
    Range("B2", Range("F" & Rows.Count).End(xlUp)).Sort [F2], 2
    End Sub
    I think these improvements will help your file out no end. I have tested the above against your formula and reconciliations happen.

    I also aligned the Data and Results table so they all start in the same place. This is a better way I feel. I have attached the model for you to peruse.

    Take care

    Smallman
    Attached Files
    sigpicthesmallman.com .......... Excel Dashboards ............ Excel Infographicsg..........k

    Comment


    • #3
      Re: World Cup Fantasy Football

      Hi There, Thanks for that it does the trick.

      Comment


      • #4
        Re: World Cup Fantasy Football

        Hi All, I know this a bit cheeky but I'd love to merge my programme with this old electronic wallchart from another members post back in 2006. (see attached link)

        http://www.ozgrid.com/forum/showthre...ntasy+football

        Hope someone can help.

        Many Thanks

        Gary Hunt

        Comment


        • #5
          Re: World Cup Fantasy Football

          Hi Gary

          I can't access that document from work but will have a look tonight when I get home and get back to you. We all love football.

          Take it easy.

          Smallman
          sigpicthesmallman.com .......... Excel Dashboards ............ Excel Infographicsg..........k

          Comment


          • #6
            Re: World Cup Fantasy Football

            Gary

            Can you post the file on here as even from my home computer I can not see a file in that site.

            Take care

            Smallman
            sigpicthesmallman.com .......... Excel Dashboards ............ Excel Infographicsg..........k

            Comment


            • #7
              Re: World Cup Fantasy Football

              World Cup 2014 v6.zip
              Hi there, It appears that this file no longer exists on this forum. Attached is what I would like to add to this programme. I've added a new tab called 'Match Order' and I would like the data from this tab to copy the results to the 'Results' tab.
              Also how do you make the programme copy the correct Country to the correct cell after the first round matches are all done ? There must be 100's of either or permatations.
              Is it also possible to make the mini tables on the 'Match Order' tab to work after the results have been added to this tab ?
              I realise I'm asking an awful lot here, but if you guys can crack this it would make this programme complete. If this can't be done automatically, it can still be done manually.
              Even if this can't be done I still feel that this is a good little programme that you could run in your work place, local pub, or amongst your friends and family.

              Many Thanks
              Gary Hunt

              Comment


              • #8
                Re: World Cup Fantasy Football

                Hi Gary

                I had a look at your table and the file in a bit more detail and was inspired to create a world cup wall chart in Excel for Brazil. The file can be found here. Perhaps you could incorporate your data into this file which I have made if it has not come too late.

                http://www.thesmallman.com/#!world-cup-in-excel/c1t2j

                I am keen to hear if you find anything which does not look right or you think I could improve.

                Take care

                Smallman
                Last edited by Smallman; May 26th, 2014, 20:22.
                sigpicthesmallman.com .......... Excel Dashboards ............ Excel Infographicsg..........k

                Comment


                • #9
                  Re: World Cup Fantasy Football

                  Well done Marcus, this is very similar to the one I sent a link to. Do you think it is possible to add in my programme to yours ? copying the No. of Wins, Draws, and Goals to the 'Results' tab of my programme, so that automatically updates the league table on my programme.

                  Many thanks for this programme
                  Gary Hunt

                  Comment


                  • #10
                    Re: World Cup Fantasy Football

                    Hi Gary

                    Yeah just highlight all of the sheets you want in the file I provided, then go move or copy. Make sure you choose Move Sheets. Now all of your sheets are in my model. So just show the sheet tabs add a few links to the other sheets, badge your sheets so they look and feel like the other sheets and you should have a fully integrated model in about 10 minutes.

                    Give us a shout if it does not work as expected. I think you should have a go first and love to hear how it goes.

                    Take care

                    Smallman
                    Last edited by Smallman; May 27th, 2014, 10:14. Reason: typo
                    sigpicthesmallman.com .......... Excel Dashboards ............ Excel Infographicsg..........k

                    Comment


                    • #11
                      Re: World Cup Fantasy Football

                      Hi there, I seem to have problems with the download. it always returns a "Run-Time Error 1004" I don't know if this is a fault with my pc or a corrupt file. can you please check your file.



                      Thanks
                      Gary

                      Comment


                      • #12
                        Re: World Cup Fantasy Football

                        Gary where do you get the run time error? What conditions. I will have a look but that info is important thanks.

                        Smallman
                        sigpicthesmallman.com .......... Excel Dashboards ............ Excel Infographicsg..........k

                        Comment


                        • #13
                          Re: World Cup Fantasy Football

                          Hi there, I get the error as soon as the programme runs. I've tried running straight away and saving it first, and the running it. Still I get the same error.

                          Thanks for getting back to me.


                          Gary

                          Comment


                          • #14
                            Re: World Cup Fantasy Football

                            Yes you are right Gary. Thanks so much for the spot. If the active sheet when you save is the Instructions page this line

                            Sheet7.activate (sheet7 is the Instructions page) in the open workbook event won't work. I have change it to this line

                            Code:
                            Application.Goto Sheet7.Range("A1"), True
                            now it does work, I think. Can you test it please Gary.

                            I am going to write an article about this topic.

                            Thanks again

                            Smallman
                            sigpicthesmallman.com .......... Excel Dashboards ............ Excel Infographicsg..........k

                            Comment


                            • #15


                              Re: World Cup Fantasy Football

                              Hi Garry

                              How did you go incorporating the worksheet. I was interested in how it all ended up. Only 10 days to go till kick off

                              Smallman
                              Last edited by Smallman; June 3rd, 2014, 09:40. Reason: typo
                              sigpicthesmallman.com .......... Excel Dashboards ............ Excel Infographicsg..........k

                              Comment

                              Working...
                              X