Results 1 to 13 of 13
  1. #1
    Lounger
    Join Date
    Dec 2000
    Location
    North Epping, New South Wales, Australia
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    handicap (excel 2007)

    I am stuck with a handicapping program. I am not an Excel expert just an old bloke tinkering away!

    As per attached - I am trying to get Excel to read the data from lines 1,2,3,4 etc. into the Existing Ratings group (line 1 has been calculated), which then calculates the New Ratings and automatically places them in the columns on the left.

    Thanks
    Attached Images Attached Images
    <img src=/S/gramps.gif border=0 alt=gramps width=20 height=20> <font face="BrushScript BT"> Cees </font face=brush>
    <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18> <img src=/S/flags/Netherlands.gif border=0 alt=Netherlands width=30 height=18>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: handicap (excel 2007)

    You'll probably have a better chance of getting help if you attach a sample workbook.

  3. #3
    Lounger
    Join Date
    Dec 2000
    Location
    North Epping, New South Wales, Australia
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: handicap (excel 2007)

    Thanks Hans,

    Can I put a sample within the 100k?

    Cheers
    Cees
    <img src=/S/gramps.gif border=0 alt=gramps width=20 height=20> <font face="BrushScript BT"> Cees </font face=brush>
    <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18> <img src=/S/flags/Netherlands.gif border=0 alt=Netherlands width=30 height=18>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: handicap (excel 2007)

    Create a copy of the workbook and remove parts that aren't relevant to your question.
    If the saved copy is still too large, create a zip file from it and attach that.

  5. #5
    Lounger
    Join Date
    Dec 2000
    Location
    North Epping, New South Wales, Australia
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: handicap (excel 2007)

    Hans,

    Attached a copy of the workbook.
    I am trying to get the data from lines 1, 2, 3, etc in the "input data table and match records" automatically to be calculated in the "output data - ratings" table and then have the new ratings transferred to into the date columns.

    Does this help?

    Cheers
    Cees
    Attached Files Attached Files
    <img src=/S/gramps.gif border=0 alt=gramps width=20 height=20> <font face="BrushScript BT"> Cees </font face=brush>
    <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18> <img src=/S/flags/Netherlands.gif border=0 alt=Netherlands width=30 height=18>

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: handicap (excel 2007)

    Can you attach a copy saved as an XL2003 or earlier version for those of us without XL2007 or does it require XL2007 features to use?

    Steve

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: handicap (excel 2007)

    I can open your workbook in Excel 2003 using the converter, but it uses new functions introduced in Excel 2007, so I can't do much with it. I hope that someone who has Excel 2007 will look at it.

  8. #8
    Lounger
    Join Date
    Dec 2000
    Location
    North Epping, New South Wales, Australia
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: handicap (excel 2003)

    Sorry about the 2007.
    I have saved it as a 2003 file. Hope this helps.
    So much for me tinkering (and learning) with Excel.

    Cheers
    Cees
    Attached Files Attached Files
    <img src=/S/gramps.gif border=0 alt=gramps width=20 height=20> <font face="BrushScript BT"> Cees </font face=brush>
    <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18> <img src=/S/flags/Netherlands.gif border=0 alt=Netherlands width=30 height=18>

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: handicap (excel 2003)

    The problem is the same: the Excel 2007 workbook uses the IFERROR function and this causes #NAME when you convert the workbook to Excel 97-2003 format. However, I wrote a macro to convert the formulas to use IF and ISERROR instead of IFERROR, so I can now see the same results as you. I have attached the converted workbook for if others want to take a look. The second sheet contains the text of the macro.

    But I still don't understand what you want. As far as I can tell you already have formulas that transfer the values from the Input Data area to the areas above and from there to the columns on the left. Could you try to explain in more detail what you want to accomplish?
    Attached Files Attached Files

  10. #10
    Lounger
    Join Date
    Dec 2000
    Location
    North Epping, New South Wales, Australia
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: handicap (excel 2003)

    Thanks Hans. I went out and bought a John Walkenbach's book - "Favorite Excel 2007 Tips @ Tricks" and as you explained the iferror is new to 2007!

    You are right I can get the values from line 1, eg N35 (14) into H23 to get name and ratings from table (Palmer 1000) into R21 and U21( new rating of 1035) and transferring 1035 to E17.

    I have about 14 matches per day, giving 14 lines of input, and want to keep them in my database for future reference and if need to make changes to Tables 1 to 4.

    So when all the data of the days matches are typed in lines 1, 2,3 , etc., how can Excel read the data per line and recalculate the results and update the list in the Date columns "E,F,G, etc". (or get data from e.g as per line 2, N36 into H23 into E41 without retyping them in the OUTPUT DATA -RATINGS table). Is there a formula or macro what will do this automatically?

    Cheers
    <img src=/S/gramps.gif border=0 alt=gramps width=20 height=20> <font face="BrushScript BT"> Cees </font face=brush>
    <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18> <img src=/S/flags/Netherlands.gif border=0 alt=Netherlands width=30 height=18>

  11. #11
    Lounger
    Join Date
    Dec 2000
    Location
    North Epping, New South Wales, Australia
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: handicap (excel 2003)

    Hans, thanks for the macro. the IFERROR is now I(ISERROR.... etc.).
    Still I have a few more question:

    Cells U21:U24 and U26:U29 give me new ratings for teams A and B. These values are then transferred to cells in column E.
    How can these value become permanent in Column E and the values in Cells U21:U24 and U26:U29 become blank ready for the next input?

    Cheers
    Attached Files Attached Files
    <img src=/S/gramps.gif border=0 alt=gramps width=20 height=20> <font face="BrushScript BT"> Cees </font face=brush>
    <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18> <img src=/S/flags/Netherlands.gif border=0 alt=Netherlands width=30 height=18>

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: handicap (excel 2003)

    You attached the same workbook as last time (with IFERROR).

    I have attached the workbook from my previous reply with a command button labeled "Transfer Ratings". It will copy the values from U1:U24 and U26:U29 to the appropriate cells in column E using this macro:

    Sub XferRatings()
    Dim r As Long
    Dim b As Long
    Dim n As Long
    On Error Resume Next
    Application.ScreenUpdating = False
    For r = 21 To 29
    If Not r = 25 Then
    b = Range("R" & r)
    n = 0
    n = Application.WorksheetFunction.Match(b, Range("A:A"), 0)
    If n > 0 Then
    Range("E" & n) = Range("U" & r)
    End If
    End If
    Next r
    Application.ScreenUpdating = True
    End Sub

    You cannot clear the cells in U1:U29 because they contain formulas. Perhaps one could clear the cells in H35:W38, but I can't judge that. The instruction would be

    Range("H35:W38").ClearContents
    Attached Files Attached Files

  13. #13
    Lounger
    Join Date
    Dec 2000
    Location
    North Epping, New South Wales, Australia
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: handicap (excel 2003)

    By Crikey - Hans, that was a quick and very helpful reply.

    This old bloke -"haringkop uit Vlaardingen" - would have no chance of working that out.

    On ye mate!

    Cheers
    <img src=/S/gramps.gif border=0 alt=gramps width=20 height=20> <font face="BrushScript BT"> Cees </font face=brush>
    <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18> <img src=/S/flags/Netherlands.gif border=0 alt=Netherlands width=30 height=18>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •