Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Newbie

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I don't use Excel to any great extent, but I'm getting into it.

    I have this little problem that I cannot figure out.

    ON this page (web) http://teetyme.com/outdoorsims/teamscores_april2.htm

    I wish to add another set of cells that reflect the totals of the teams scores, and then sort those cells. I can sort each team fine. Sorting on Total of each player works great. I can make the additional cells and get the correct total for each team to appear there using a (SUM) formula.

    Now here's my problem.... When I sort the new cells with the team totals to show which team is leading, it will only work the first sort. If I sort again, the formula has changed and I get different numbers as results. When I check the formula, it is n't the same as I originally enter. The range has changed. It'll do the same if I just use total1+total2+total 3.

    The sheet being displayed now works fine. And it will work after I add the additional total cells. The thing that doesn't work is the sort on the new tabl of Team toals, which does not appear on the web site, since I cannot get it to work.

    To explain it better:

    I want to sort the players totals in a decending sort. I want the lower rh cell in each table to reflect the Sum of all the players 3 day totals. That part works. What I need to do is take the Team Totals and make a new table reflecting the team name and it's total, then sort that table on the team totals in a decending sort. When I try it, it works one time, the next time the formula has changed to a different range.

    I hope I explained this right.

    [ This Message was edited by: Pegleg on 2002-04-26 21:17 ]

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Pegleg,

    I'd go with =RANK and =VLOOKUP, so you don't have to keep sorting each time...

    something like this maybe :























    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    1
    =RANK(C1,$C$1:$C$8,0)Team Blue255 1=VLOOKUP(E1,$A$1:$C$8,2,0)=VLOOKUP(E1,$A$1:$C$8,3,0){--- copy down
    2
    =RANK(C2,$C$1:$C$8,0)Team Red326 2=VLOOKUP(E2,$A$1:$C$8,2,0)=VLOOKUP(E2,$A$1:$C$8,3,0)
    3
    =RANK(C3,$C$1:$C$8,0)Team Green612 3=VLOOKUP(E3,$A$1:$C$8,2,0)=VLOOKUP(E3,$A$1:$C$8,3,0)
    4
    =RANK(C4,$C$1:$C$8,0)Team Yellow554 4=VLOOKUP(E4,$A$1:$C$8,2,0)=VLOOKUP(E4,$A$1:$C$8,3,0)
    5
    =RANK(C5,$C$1:$C$8,0)Team Brown812 5=VLOOKUP(E5,$A$1:$C$8,2,0)=VLOOKUP(E5,$A$1:$C$8,3,0)
    6
    =RANK(C6,$C$1:$C$8,0)Team Orange362 6=VLOOKUP(E6,$A$1:$C$8,2,0)=VLOOKUP(E6,$A$1:$C$8,3,0)
    7
    =RANK(C7,$C$1:$C$8,0)Team Black222 7=VLOOKUP(E7,$A$1:$C$8,2,0)=VLOOKUP(E7,$A$1:$C$8,3,0)
    8
    =RANK(C8,$C$1:$C$8,0)Team Purple226 8=VLOOKUP(E8,$A$1:$C$8,2,0)=VLOOKUP(E8,$A$1:$C$8,3,0)
    9
    (linked from their total)
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20


    which'll look something like this :























    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    1
    6Team Blue255 1Team Brown812
    2
    5Team Red326 2Team Green612
    3
    2Team Green612 3Team Yellow554
    4
    3Team Yellow554 4Team Orange362
    5
    1Team Brown812 5Team Red326
    6
    4Team Orange362 6Team Blue255
    7
    8Team Black222 7Team Purple226
    8
    7Team Purple226 8Team Black222
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20




    _________________
    Hope this helps,
    Chris


    [ This Message was edited by: Chris Davison on 2002-04-26 22:08 ]

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hmmmm, looks a little complicated for a person of my skill level. But, let me play with it. I thank you for taking the time to reply.

    My other alternative is converting to a mySQL/PHP page for it, since my server doesn't support Access.

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    yeah, just enter the formula in a1 and copy down to a8, ditto the formulas in f1 and g1 and you're all set....

    it'll resort itself as the totals change which is the handy thing

    ps - team yellow needs to change it's bait or something !


  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yea, team Yellow is on shore drinking all the beer, LOL.

    After reading what you said, a light went off in my head! "LINK"

    So I added the table and used "=Sheet1!K12" etc and the darn thing worked. You can see it using my link again:

    http://teetyme.com/outdoorsims/teamscores_april2.htm

    I do, however like what you have done, and I'm gonna try to implement it. Or, I can use macros, once I learn how to link macros to run one after another, or use one large macro.

    I was dreading writing the php code for this thing... Guess I don't have to NOW!

    Question: Using your method, do both new tables show up on the page?

  6. #6
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Chris,

    How did you post the screen prints?

  7. #7
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    OK, Chris, got it to work with macros. Did a macro for each team, then a macro for the Leaderboard. Then combined them using the editor. Works great. I can enter the scores, then press one key to run the combined macro, hit save and I'm done.

    Thanks for waking up my brain!

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-27 00:07, Joe Was wrote:
    Chris,

    How did you post the screen prints?
    Hi Joe,

    they're not actually screen prints as such (although that makes me a smile as it shows my design is pretty good!)

    it's actually a table in HTML (this board allows HTML commands) that I've automated using excel.... if you want the file (it's only small) repost and I'll send, although I only finished it this morning so will probably fiddle around more over the weekend. First sheet is your spreadhseet, 2nd sheet is the resulting HTML which you just copy into the board message...

    Ivan's also looks good, with colours n stuff, I'd recommend checking his link out first though :

    http://www.mrexcel.com/board/viewtop...11&forum=10&10


    :: Pharma Z - Family drugstore ::

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-27 00:14, Pegleg wrote:
    OK, Chris, got it to work with macros. Did a macro for each team, then a macro for the Leaderboard. Then combined them using the editor. Works great. I can enter the scores, then press one key to run the combined macro, hit save and I'm done.

    Thanks for waking up my brain!
    No worries ! Site looks good too

    (my personal best was a 2lb 2oz Pike and it was <---------------------------------- this -------------------------------> long)



    [ This Message was edited by: Chris Davison on 2002-04-27 05:56 ]

  10. #10
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This long huh, lol. Thanks for the kind words regarding the site. Now get this, the site is for a pc game called Trophy Bass 4, a virtual fishing game. (wink)

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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