1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Advice on Creating an Easily updated Excel Table

Discussion in 'Business Applications' started by crawdad, Feb 11, 2005.

Thread Status:
Not open for further replies.
Advertisement
  1. crawdad

    crawdad Thread Starter

    Joined:
    Sep 24, 2001
    Messages:
    306
    Hello all, Im looking for a way to create an excel table that be really easy to update once built, and was hoping some excel experts could help me out. The reason being is that it will be a pretty large table. Here is what Im trying to accomplish.

    The table will have about 150 teams. I want to build something that looks like a multiplication table. So the list of 150 teams will be listed in rows 1-150, and in the first 150 columns across the top. The object would be to be able to see what each teams record is against the other teams. For example, if I go to cell F75, I would be able to see the win/loss record of TeamX vs TeamY (13-6 for example).

    Is there anyway to set this up with formulas that will make it easy to maintain? Im open to any ideas since I cant think of any easy way to do it. Thanks much for any help.
     
  2. wolfworx

    wolfworx

    Joined:
    Oct 2, 2001
    Messages:
    1,216
    Need more information.

    Do you presently have data in your 150x150 grid? What does it look like? i.e is it a single number or a score result such as "14-6" ? If it is the latter it would probably need to be entered as a label and thus useless for subsequent calculations.

    Do all the 150 teams play all the other 150 teams? Will all the cells in the grid be ultimately filled? Later updated by replacing old contest results with recent ones?

    How would you like to enter/update data?

    How would you like to query your table for the result of a two team contest?

    I have created an Excel worksheet a few years ago to track the NFL teams for a season, but it requires entry of the season schedule to a table. I had to make 16 entries for each team (30 that year) manually. When the season started I could enter the weekly results into a table. At any time during the season, I could select a team from the list and view the game results to date and the future matchups. I could also enter a betting line to predict game results.

    A single season of NFL games is a muche smaller table than yours of 150x150. Manual entry to your table would be extremely time consuming. :eek:

    Do you REALLY want to do this???
     
  3. crawdad

    crawdad Thread Starter

    Joined:
    Sep 24, 2001
    Messages:
    306
    Thanks for your help Wolf. I have already resigned myself to the fact that the initial build is going to be a pain. Once its done it will probably be updated once a month. To anwser your questions:

    "Do you presently have data in your 150x150 grid? What does it look like? i.e is it a single number or a score result such as "14-6" ?"

    Right now I have all the teams listed down the left side(rows), and also along the top(columns). Thats all I have so far.

    Do all the 150 teams play all the other 150 teams? Will all the cells in the grid be ultimately filled? Later updated by replacing old contest results with recent ones?



    All 150 may or may not play all other teams. Maybe at some point because it will be an ongoing thing. All the cells in the grid might be eventually filled but it will take awhile. The cells will be filled with the career win/loss record of the two teams who meet in that cell. (for example, TEAM A crosses paths with TEAMC in cell R45, TEAMA has a beaten TEAMC 6 out of 9 metings so the cell would be filled with 6-3. For TEAM C's row, where it meets TEAMA, it will be filled with 3-6) Hope that maes sense. ;)

    How would you like to enter/update data?

    Well this was kind of why I asked this question, Obviously I could go through and manually change the cells as records change. However I was hoping some Excel Guru could look at my problem and have a better way to do it. At the very least I was hoping there might be a way to have a corresponding cell autofill. Like in the example above, when I enter 6-3 in TEAMA's cell, the TEAMC cell would autofill with 3-6. I cant imagine how that would be possible though.

    How would you like to query your table for the result of a two team contest?

    Im not too worried about this, I can just scroll around to find what I want.

    I hope this makes sense, Im sorry the response is so long. I appreciate your help.
     
  4. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    The only way I can think to do it would be to reference a datasheet with a separate listing of each team by all the others (with just one row to space between each data group, that would be 22,650 rows deep!) You could have a win and a loss column against each team and a 3rd column showing wins,"-", losses. This is the cell you could reference on the main sheet.
    But sheesh, are you sure you want to do this?!?!?!!!!
     
  5. crawdad

    crawdad Thread Starter

    Joined:
    Sep 24, 2001
    Messages:
    306
    Hmm, that doesnt really sound very fun. I was hoping there was some shortcut I didnt know about. Oh well. Thanks for your help anyway though.
     
  6. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Well, there may be some way - ask Xcelguru or one of those brainy ones...and good luck!
     
  7. crawdad

    crawdad Thread Starter

    Joined:
    Sep 24, 2001
    Messages:
    306
    Will do. thanks Slurpee
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/329440

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice