Forums

Excel help required for league tables & fixtures

• ScottHope 10,122 Posts

  Wed, May 25 2016 1:50 AM

  oneeyedjohn:

  So have I corrupted the formulae somehow.

  Possibly, as I'm sure you know, excel formulas use cell references, but if you have tables, the table name comes into it as well.

  Modifying other peoples worksheets is always going to be a bit of a mare.

  Hopefully Alan will put you right, let us know how you get on.  : )

• WigerToods2010 8,445 Posts

  Wed, May 25 2016 4:42 AM

  ScottHope:

  Modifying other peoples worksheets is always going to be a bit of a mare.

  Very true.

  Had a look and I think it has to do with data being placed in wrong cell/sheet when adding names.

  Weird that it works initially then fails once attempting to sort league standings.

  As you say it'll be sorted by the original author... unless he's still in the pub.

  :)

   

• ScottHope 10,122 Posts

  Wed, May 25 2016 6:04 AM

  WigerToods2010:

  ... unless he's still in the pub.

  Did he go there because of what John sent him?  ; )

• WigerToods2010 8,445 Posts

  Wed, May 25 2016 8:57 AM

  

  Cheers.

  :)

• andyson 6,415 Posts

  Wed, May 25 2016 11:29 AM

  I'm looking forward to learning what Alan Murray finds.

• oneeyedjohn 9,567 Posts

  Wed, May 25 2016 12:52 PM

  andyson:

  I'm looking forward to learning what Alan Murray finds.

   

  Well, here it is.........

   Hi John,

  
  I believe the attached has it working well. On the Calculations sheet in column A the formula was as below. The row number was just a little short and needed changing.
  
  =IF(Teams!A2="","",RANK($B3,$B$3:$B$20,1)+COUNTIF($B$3:$B3,$B3)-1)
  
  to this
  
  =IF(Teams!A2="","",RANK($B3,$B$3:$B$22,1)+COUNTIF($B$3:$B3,$B3)-1)
  
  I do believe it is ok now. Any problems let me know. 
  
  Using Tables as you mention in your previous email can be beneficial. It can make the formulas easier to work with.
  
  Kind regards

  
  

   

• ScottHope 10,122 Posts

  Wed, May 25 2016 2:28 PM

  That's over me 'ead, but hope it works okay for you now John.

• WigerToods2010 8,445 Posts

  Wed, May 25 2016 4:30 PM

  Nice to see a happy ending/outcome on here. Good luck with the event, John.

• oneeyedjohn 9,567 Posts

  Wed, May 25 2016 4:42 PM

  Yep, it is very satisfying indeed.

  For an event that will run for 19 weeks, I welcome all the automation I can get.

  All I do is bang in the results and  voilà !