+ Reply to Thread
Results 1 to 20 of 20

Data Extract from Multiple sheets with indirect index match function

  1. #1
    Registered User
    Join Date
    09-08-2018
    Location
    Baltimore, MD
    MS-Off Ver
    Office 2010
    Posts
    20

    Data Extract from Multiple sheets with indirect index match function

    Previously, I was asking for suggestions - now looking for ways to improve what I have created. It's basic but It does work, but each week there are things that must be copy/pasted - "Previous AVG & Previous HDCP." I would like to use "last week's AVG/HDCP" on this weeks "scores/games." Instead of having a spreadsheet for all possible matches, is it possible to somehow create a "weekly report" by selecting the two team numbers. Any other suggestion/help would be greatly appreciated.
    Attached Files Attached Files
    Last edited by Pepe Le Mokko; 05-24-2019 at 11:41 AM.

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Wii Bowling League for Senior Center

    New title more descriptive.
    Last edited by Pepe Le Mokko; 05-24-2019 at 11:42 AM.

  3. #3
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: Wii Bowling League for Senior Center

    Accordingly to your file, i suggest title as :
    "Data Extract from Multiple sheets with indirect index match function"


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  4. #4
    Registered User
    Join Date
    09-08-2018
    Location
    Baltimore, MD
    MS-Off Ver
    Office 2010
    Posts
    20

    Re: Wii Bowling League for Senior Center

    Thanks for three the comments about renaming “my thread” The last one, did give me an idea of what I’m looking for in my original question (Data Extract from Multiple sheets with indirect index match function”). As a basic user of MS Excel, I have never heard of “Data Extract” or “Indirect Index,” so how could I have used those words in MY THREAD. Thought this forum was for MS EXCEL “beginners” to get help, no experts who run into problems. Therefore, at the age of 80 and time is a concern, please remove my query and my login “ELCOYOTE” from this forum! BYE!

  5. #5
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Wii Bowling League for Senior Center

    Hi
    sorry you feel that way.
    No one expects such a detailed title as avk provided, but a succinct description of what you are trying to do. ( Google search like)
    This helps getting faster responses ( members usually look at titles first) and facilitates searching for other members or even visitors.
    Suppose only members interested in bowling open your thread....
    I will change the title to what avk suggested and hope you will change your mind
    As for the rest, it is up to you to pursue or not
    Cheers

  6. #6
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Data Extract from Multiple sheets with indirect index match function

    @Elcoyote: Bear with us a little bit longer. I think it's great that you are taking this on! I have been looking at your provided data and may have some thoughts - I need to do further work though.

    Meanwhile you do need to make an update to the thread title per the moderator's request before I or anyone else is allowed to post any help.

    There is no need for the title to assume the use of particular functions. Here's another title suggestion: "Record Wii bowling league match scores and provide summary results and standings"

    You do need to change the title before any further help can be provided. Do it and then we'll see if what I suggest above for a title passes muster with the moderators. Once the moderators approve then people here can dive in and help further.

  7. #7
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Data Extract from Multiple sheets with indirect index match function

    @Geoff
    I changed the title already. You can have a go at it whenever you feel like it

  8. #8
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Data Extract from Multiple sheets with indirect index match function

    @Elcoyote: OK - I didn't see Pepe's comment before posting the above. Confirm you're still with us and I (and I'm sure others) will continue looking at your file.

  9. #9
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Data Extract from Multiple sheets with indirect index match function

    Quote Originally Posted by Pepe Le Mokko View Post
    @Geoff
    I changed the title already. You can have a go at it whenever you feel like it
    Thanks Pepe

  10. #10
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Data Extract from Multiple sheets with indirect index match function

    @Elcoyote: OK, here for starters is a formula to calculate the previous week averages:
    It's long but it's really just very similar to the A4:D7 formulas three times over. I did change the row references from absolute to relative so that the formula can be copied down from row 4 to row 7 without change. So the formula in F4 copied down to F7 is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    This yields the same values as your manually entered numbers.

    Let me know if this works for you.

    I will look now at automating the "Previous Handicap" column - that looks to be a little harder!

  11. #11
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Data Extract from Multiple sheets with indirect index match function

    Actually "previous handicap" isn't so bad. In G4 and copied down to G7:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    This yields the same results as your manually entered numbers.

    Let me know if this works for you.

    Next I will think about your request to eliminate the match-by-match worksheets. This could be a fair amount of work, so please confirm you are still monitoring this thread before I go too far.
    Last edited by GeoffW283; 05-24-2019 at 01:30 PM.

  12. #12
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: Data Extract from Multiple sheets with indirect index match function

    I have some modify your data structure.
    See calculation & summary sheet.
    In calculation sheet : Using indirect index match function as per sheet name data extract.
    In summary sheet : You need to change in "A1" & "A9" with data validation.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    09-08-2018
    Location
    Baltimore, MD
    MS-Off Ver
    Office 2010
    Posts
    20

    Re: Data Extract from Multiple sheets with indirect index match function

    Thank You, all! The understanding and help this forum has provided, has made me to reconsider and re-enter the building/forum. Special thanks to "Pepe Le Mokko, AVK and GeoffW283 for their involvement and putting up with this old man. Looks like I'm need to do some real reading/learning. Thxs again!

  14. #14
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Data Extract from Multiple sheets with indirect index match function

    Glad you are with us again! Take a look at what has been provided so far and let us know if you have further questions.

  15. #15
    Registered User
    Join Date
    09-08-2018
    Location
    Baltimore, MD
    MS-Off Ver
    Office 2010
    Posts
    20

    Re: Data Extract from Multiple sheets with indirect index match function

    Have looked at your suggested changes and they look great. In the "calculation sheet" for Team 2, there is a minor error with the row reference - but I can correct that! Have been reading about "Indexing and Matching" it appears the way to go, instead of "V or HLOOKUP." Would you mind explaining something: (1) How/where did you renamed my Team sheets to P1, P2, etc. (2) In your formula, you reference "H:H," where are they? And (3) you created a column in the "Calculation" sheet with "Team1, Team2, etc." Must believe you will somehow arrange that by selecting 2 teams, their scores/results will be displayed and the "Summary" will be updated so that I can print them out. Thanks Again!

  16. #16
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Data Extract from Multiple sheets with indirect index match function

    Your questions relate to AVK's proposed solution, but, pending his response, I'll try my best to answer your questions.

    The numbers below map to your numbered questions.

    1. There is no renaming of the team worksheets. I think you are referring to cells like P2 and P3 on the "Calculation" worksheet. P2 has the formula: =INDEX(INDIRECT(P$1&"!H:H"),0). This formula is using the indirect() function to address the TEAM2 worksheet. Specifically it is getting the worksheet name from cell P1 which contains "TEAM2". Look up the help on indirect() for further info. It is important therefore that cell P1 exactly matches your worksheet name. This complexity could be eliminated by a more drastic re-arrangement of your data - I may look into this but it will take me a while.
    2. H:H refers to column H in its entirety, so following on from (1) above INDIRECT(P$1&"!H:H") evaluates to TEAM2!H:H, so the P2 formula becomes: index(TEAM2!H:H, 0). Now there's a final obscurity. The second parameter supplied to index() usually specifies which element of the array supplied as the first parameter to index() is to be used, so for P2 we could have said: =INDEX(INDIRECT(P$1&"!H:H"),2). For P3 =INDEX(INDIRECT(P$1&"!H:H"),3) and so on. In this case the second parameter to index() is a special case of zero which means that index() returns the entire array specified in the first parameter. The motivation is to allow the formula in P2 to be copied down to P5 without change - always a good thing to do for ease of maintenance. I think in this case though AVK could have simplified the P2 formula to: =INDIRECT(P$1&"!H:H"). That looks to me like it achieves the same result.
    3. I think AVK's "Calculation" worksheet is an intermediate calculation that you can disregard. On the "Summary" worksheet click on Cell A1. That will give you a dropdown list allowing you to select a particular team. Cell A9 similarly allows a second team to be picked. The rest of the tables on the "Summary" worksheet are updated with the appropriate values for the selected teams. If you go back to the "Calculations" worksheet you will see that those values have been updated according to the teams selected on the "Summary" worksheet. I'm not sure why the "Calculations" and "Summary" worksheets can't be consolidated into one worksheet. Maybe AVK can clarify!


    Final thought for now: take a look at how AVG and HDCP are calculated on the "Calculation" worksheet. Shouldn't they be the values that were in effect on the date that this particular match was played? If so then I don't think that's what is happening now (this is also different to the formulas that I provided in posts #10 and #11 which computed the AVG and HDCP for the previous match played - which is not the same as the previous match played against a particular team.

    AVK: If I have gone astray or missed some key points then please correct me.

  17. #17
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: Data Extract from Multiple sheets with indirect index match function

    Dear Geoffw : You are clear all points.
    Please Login or Register  to view this content.
    Not any particular, thst is only provide two different way.

  18. #18
    Registered User
    Join Date
    09-08-2018
    Location
    Baltimore, MD
    MS-Off Ver
    Office 2010
    Posts
    20

    Re: Data Extract from Multiple sheets with indirect index match function

    I'm starting to understand what is happening - P1 & HH are somewhat an "Invisible notbooks" that can retrieve/record from multi-spreadsheets and pass info on to another workbook! Referencing my using the previous weeks average, something I made up to punish bowers. However, I will wait until the bowler has bowled at least 5 or 6 games to establish their up-to-dated average. Meaning going back to redo all scores/results. I have each bowlers average from 2018 so no problem for us; but other centers will probably have to use the 5/6 game to get their current average.
    Still Learning................Thx!!!!

  19. #19
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Data Extract from Multiple sheets with indirect index match function

    There is nothing special about P1 itself. It's just a regular cell. It can contain TEAM1 or TEAM2 or TEAM3 etc. H:H is a range representing all of column-H. The syntax to refer directly to, say, column H of worksheet TEAM2 would simply be TEAM2!H:H The trouble is that for your application the P2 formula can't use this kind of direct reference because which worksheet to refer to is unknown until you the user specify which team you are interested in seeing (via Summary!A1 and Summary!A9). Hence the use of the indirect() function. So for P2 you have: INDIRECT(P$1&"!H:H"). P$1 evaluates to whichever team you have selected via the Summary worksheet, say, TEAM1. "&" is the concatenation operator, "!H:H" is just a string and so the reference becomes: TEAM1!H:H
    So in fact it's the indirect() function that is doing the "magic" to create a dynamic reference!

    It sounds like you have the averaging/handicapping under control for now. Do you have any more specific questions at this point? If so then let us know, if not then possibly at this point you should mark this thread as solved and then come back to us if you encounter new issues.
    Geoff

    Did I help significantly? If you wish, click on * Add Reputation to say thanks.
    If your problem has been resolved please select ?Solved? from the Thread Tools menu

  20. #20
    Registered User
    Join Date
    09-08-2018
    Location
    Baltimore, MD
    MS-Off Ver
    Office 2010
    Posts
    20

    Re: Data Extract from Multiple sheets with indirect index match function

    Thanks again for all the help! Especially - AVK, Pepe Le Mokko and GeoffW283.......ElCoyote is happy!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Bowling League Record Keeping.
    By Elcoyote in forum Excel General
    Replies: 3
    Last Post: 05-15-2019, 10:08 PM
  2. Hi Every one Rank Lawn green bowling league table
    By jtd Clarke in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 11-07-2016, 05:14 AM
  3. I want all open files to show in a bar
    By Siegfried in forum Excel General
    Replies: 1
    Last Post: 12-04-2012, 06:58 PM
  4. Bowling league
    By mutant04 in forum Excel General
    Replies: 1
    Last Post: 02-23-2008, 08:23 PM
  5. IF AND - a senior moment
    By johnnywinter in forum Excel General
    Replies: 2
    Last Post: 09-11-2007, 08:36 PM
  6. How do I set up a bowling schedule for an eight team league
    By Oniram in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-16-2005, 02:05 AM
  7. Replies: 1
    Last Post: 03-29-2005, 08:06 AM

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