+ Reply to Thread
Results 1 to 11 of 11

Summarizing data from multiple sheets into one sheet

Hybrid View

  1. 09-09-2011, 02:38 PM #1
    Irish Pat
    Irish Pat is offline
    Registered User
    Join Date
    12-05-2010
    Location
    Dublin,Ireland
    MS-Off Ver
    Excel 2010
    Posts
    18

    Angry Summarizing data from multiple sheets into one sheet

    Greetings to everyone and apologies if my explanation does not appear to make any sense, as a result I have attached a copy of my workbook(marked up with problems) with which I need urgent assistance please.
    I think all my problems are VLOOKUP related, but if you could be kind enough to check out the workbook and maybe offer some advice, then it would be very much appreciated. I have tried for a couple of weeks now to get my head around the workings of VLOOKUP, but unfortunately all I got were headaches.

    Many thanks in advance,

    Pat
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 09-09-2011, 02:50 PM #2
    abousetta
    abousetta is offline
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: VLOOKUP problem from an unitiated user

    Hi,

    Could you explain what you are trying to do with formula?

    =IF(E3=0,"",IF(D3=1,VLOOKUP(E3,group1,2),IF(D3=2,VLOOKUP(E3,group2,2),VLOOKUP(E3,group3,2))))
    Who is group1, group2, etc as I don't see any named ranges assigned these names?

    abousetta
    Please consider:

    Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
    Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.
    Reply With QuoteRegister To Reply
  3. 09-09-2011, 02:51 PM #3
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: VLOOKUP problem from an unitiated user

    Are you looking for the Sum of scores from each sheet, since some of the names appear multiple times?

    In B4 of Results:

    =SUMIF(INDIRECT("'"&SUBSTITUTE(B$2,"/",".")&"'!C3:C275"),A4,INDIRECT("'"&SUBSTITUTE(B$2,"/",".")&"'!F3:F275"))

    copied down and across
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
    Reply With QuoteRegister To Reply
  4. 09-09-2011, 03:16 PM #4
    Irish Pat
    Irish Pat is offline
    Registered User
    Join Date
    12-05-2010
    Location
    Dublin,Ireland
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: VLOOKUP problem from an unitiated user

    It is a horse racing game called Hooves of Fire and we have a group that compete against each other 3 times per week. There are 3 types of races, Group 1 Horses (1st = 35, 2nd = 25 & 3rd = 15), Group 2 Horses (1st = 25, 2nd = 15, & 3rd = 10) and Group 3 Horses (1st = 15, 2nd = 10 & 3rd = 5), this is the score structure for each particular race.On the results tab I have a list of all our group members and I would like their multiple scores in each days race meeting to to total opposite their respective names on the results sheet. Hope that helps to explain what I am after and thanks for the response.
    Reply With QuoteRegister To Reply
  5. 09-09-2011, 03:20 PM #5
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: VLOOKUP problem from an unitiated user

    So is that the SUMIF formula I gave you?
    Reply With QuoteRegister To Reply
  6. 09-09-2011, 03:29 PM #6
    Irish Pat
    Irish Pat is offline
    Registered User
    Join Date
    12-05-2010
    Location
    Dublin,Ireland
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: VLOOKUP problem from an unitiated user

    Quote Originally Posted by NBVC View Post
    So is that the SUMIF formula I gave you?
    Yes that seems to do the trick regarding the results problem, thank you very much for that ....
    Reply With QuoteRegister To Reply
  7. 09-09-2011, 03:32 PM #7
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: VLOOKUP problem from an unitiated user

    You're welcome.. is that it? Not sure if I missed something?
    Reply With QuoteRegister To Reply
  8. 09-09-2011, 03:37 PM #8
    Irish Pat
    Irish Pat is offline
    Registered User
    Join Date
    12-05-2010
    Location
    Dublin,Ireland
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: VLOOKUP problem from an unitiated user

    How do I apply that to calculate Fri 9/11 and Sat 10/11 etc....
    Reply With QuoteRegister To Reply
  9. 09-09-2011, 03:43 PM #9
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: VLOOKUP problem from an unitiated user

    A couple of things...

    1. Need Consistency. Header in C2 should be Fri 09/09 to be more consistent with sheet name. In fact if the headers exactly matched the tab names, then you would not need the SUBSTITUTE function...

    2. The tab names should not have extra trailing or leading spaces... the SAT 10.09 tab seems to have a trailing space

    3. The formula needs to be updated to make column A absolute...

    =SUMIF(INDIRECT("'"&SUBSTITUTE(B$2,"/",".")&"'!C3:C275"),$A4,INDIRECT("'"&SUBSTITUTE(B$2,"/",".")&"'!F3:F275"))

    copied across and down the table.

    If you get #REF! errors, it means either the sheet does not exist or there is not an exact match to the sheetname within row 2.
    Reply With QuoteRegister To Reply
  10. 09-09-2011, 03:46 PM #10
    Irish Pat
    Irish Pat is offline
    Registered User
    Join Date
    12-05-2010
    Location
    Dublin,Ireland
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Summarizing data from multiple sheets into one sheet

    Once again thanks very much for your help, really appreciate it.
    Reply With QuoteRegister To Reply
  11. 09-09-2011, 03:58 PM #11
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Summarizing data from multiple sheets into one sheet

    No problem...

    another tip....

    You can enter actual dates in row 2, and format the cells to appear as you desire... so after entering a real date, format cells, custom: ddd dd.mm

    Then the formula would be changed too:

    =SUMIF(INDIRECT("'"&TEXT(B$2,"ddd dd.mm")&"'!C3:C275"),$A4,INDIRECT("'"&TEXT(B$2,"ddd dd.mm")&"'!F3:F275"))

    still need consistency in tab names....
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1