Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Mar 2002
    Location
    Lebanon, Illinois, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update Distant Cell with Formula (2000SR1)

    New Guy to forum, please be kind...
    I'm creating a workbook to track fund raising efforts for AM. Cancer Society event. I have a workbook with 200 sheets, one per registered team. Each Team has 10-30 team members.
    The Coordinator needs a Team Summary sheet that provides team totals, but also needs to know the team MEMBERs that have raised the most money in 1st, 2nd a 3rd place. Without throwing 6000 names and totals on one sheet and sorting, is there a way I can convince Excel to compare each team member's total raised at the time of update to the highest so far and update the Summary?
    For example, on update of Team2!D13, compare Team2!D13 to Summary!A14, if Team2!D13>Summary!A14, make Summary!A14=Team2!D13 (amount) and make Summary!A13=Team2!A13 (Team member name) ?
    Thank you for any suggestions

  2. #2
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Update Distant Cell with Formula (2000SR1)

    Why not calculate the 1st, 2nd and 3rd place for each sheet and store the Three members names and totals in a known place on the sheet. Then you only have to work with these cells to find your overall 1st, 2nd and 3rd.

    StuartR

  3. #3
    New Lounger
    Join Date
    Mar 2002
    Location
    Lebanon, Illinois, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Distant Cell with Formula (2000SR1)

    I considered that, but the 1st, 2nd, and 3rd for the entire event may be one the same team, and I wouldn't capture that. If there really are 6000 participants, there could be ties (many individuals on many teams raising equal amounts), and I need to know that as well.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Distant Cell with Formula (2000SR1)

    Hi,

    Wouldn't it be better to have *all* data on one sheet? then getting the order of members which have raised most money can be extracted simply by sorting the sheet.

    Another great advantage is that you can use a pivot table to analyse the data by listing (e.g.) the teams individually, sorted by member performance, or any other grouping item you might have (by state, by city, etcetera).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    New Lounger
    Join Date
    Mar 2002
    Location
    Lebanon, Illinois, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Distant Cell with Formula (2000SR1)

    It appears that's the only way to accomplish it. I was trying to avoid looking for one individual in 3-4000 to update. But with team sorts and alphabetical sorts, it shouldn't be too hard. The spreadsheet will be used by Excel novices, so I was trying to make it as easy as possible.
    Thanks for taking the time to contemplate the situation.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Distant Cell with Formula (2000SR1)

    You may benefit from setting an autofilter to that sheet and explaining your users how to use it.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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