# Thread: Update Distant Cell with Formula (2000SR1)

1. ## 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. ## 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. ## 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. ## 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).

5. ## 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. ## 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.

#### Posting Permissions

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