+ Reply to Thread
Results 1 to 3 of 3

Lookup Across multiple sheets + other issues

  1. #1
    Registered User
    Join Date
    11-16-2012
    Location
    Washington, DC
    MS-Off Ver
    Excel 2007
    Posts
    2

    Lookup Across multiple sheets + other issues

    I'm going to try to explain this as best I can, though I'm admittedly rather wet behind the ears in terms of Excel functions.

    Here's what I want to accomplish, I'm wondering not only is it possible, but also perhaps some pointers in the right direction.

    I want to have multiple sheets with stats (Sports related) from each team within a specified sports league listed out in a game-by-game format. I want to create a generic formula (if possible as one formula, if not, I'm very open to creating multiples as well) that I can simply duplicate in each sheet to ping for the desired result.

    Here's what I'm specifically looking for this formula to do:
    1. Based on the team listed as the opponent, use the stats from that team's sheet (so an "if" function, possibly a lookup, not sure, that will check for the proper sheet to get the information needed)
    2. Pull the "total" stats (that is, the combined stats of every game of the season) and somehow (this is where I'm truly getting lost) subtract the game in question against the team with which the formula originated with and get an average for the remaining total stats.

    Is this possible to do? Is it possible to create a general function that I could copy/paste and make this work? Also, is there a character limit to functions? I fully expect this to get out of hand (if its possible).

    Thanks!

  2. #2
    Forum Contributor
    Join Date
    01-17-2012
    Location
    Canada
    MS-Off Ver
    Excel 365
    Posts
    221

    Re: Lookup Across multiple sheets + other issues

    Hi Mike,

    Yes it is possible and you can really go to town with a problem like this. Any chance you can upload a sample of what you have right now, or how you would like it to look?
    Regards,

    jeversf

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Lookup Across multiple sheets + other issues

    You can use the INDIRECT() function to create a link to another sheet based on a string from a cell.

    If you had "Vikings" in cell A1, and a sheet called "Vikings", you could pull back the value of cell C2 from that sheet with this formula:

    =INDIRECT("'" & A1 & "'!C2")

    You could pull back a SUM of all the values in column C from that sheet with this formula:

    =SUM(INDIRECT("'" & A1 & "'!C:C"))

    You get the idea.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1