+ Reply to Thread
Results 1 to 10 of 10

Soccer Substitution

  1. #1
    Registered User
    Join Date
    07-01-2020
    Location
    USA
    MS-Off Ver
    10
    Posts
    6

    Soccer Substitution

    Hey all,

    I've been working on modifying a workbook to track youth sports( Soccer) data. I'm not able to figure out how to make it so I can reflect a reentry into the game (getting subbed out then back in) Here is the formula I'm using currently. It's working well to track a player starting (starter) and is getting subbed out (SO) and vise versa.. the issue is when they get subbed back in (SI) it doesn't continue add the additional time played.. nor does it stop the time of a player being taken back out who was considered a sub (bench) to start the game.

    ($Q7="Starter",IFERROR(INDEX(INDIRECT("'"&$B7&"'!"&"$b25:$b74"),MATCH(F7&"SO",INDIRECT("'"&$B7&"'!"&"$d25:$d74")&INDIRECT("'"&$B7&"'!"&"$e25:$e74"),0),0),$C7),IF($Q7="Bench",$C7-IFERROR(INDEX(INDIRECT("'"&$B7&"'!"&"$b25:$b74"),MATCH(F7&"SI",INDIRECT("'"&$B7&"'!"&"$d25:$d74")&INDIRECT("'"&$B7&"'!"&"$e25:$e74"),0),0),$C7),0))

    EDIT- Added Workbook
    Attached Files Attached Files
    Last edited by gogo9; 07-07-2020 at 12:16 PM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Soccer Substitution

    Welcome to the forum.

    There are instructions at the top of the page explaining how to attach your sample workbook.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    07-01-2020
    Location
    USA
    MS-Off Ver
    10
    Posts
    6

    Re: Soccer Substitution

    Completely forgot.. Thank you.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Soccer Substitution

    You still have ... No workbook attached yet.

  5. #5
    Registered User
    Join Date
    07-01-2020
    Location
    USA
    MS-Off Ver
    10
    Posts
    6

    Re: Soccer Substitution

    Sorry had to break out a bunch of sheets.... it's a large book. I built it to track 100 games.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Soccer Substitution

    Sorry - I can't work with your colour scheme. Sends my eyes funny! I'll leave it to someone who won't get a headache looking at it. Sorry.

  7. #7
    Registered User
    Join Date
    07-01-2020
    Location
    USA
    MS-Off Ver
    10
    Posts
    6
    Quote Originally Posted by AliGW View Post
    Sorry - I can't work with your colour scheme. Sends my eyes funny! I'll leave it to someone who won't get a headache looking at it. Sorry.
    No worries, just matching club colors. Thanks for the bit help help!

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,423

    Re: Soccer Substitution

    I don't really understand how you are recording the information on the Summary sheet as there isn't a list of names to match those on the Dummy Player Stat sheet.
    That said the following may help to get the time played.
    Paste the following into cell A25 on the Summary sheet and copy down to A27 to show minutes played by Lily and Choe:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    I hope that this will be helpful.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  9. #9
    Registered User
    Join Date
    07-01-2020
    Location
    USA
    MS-Off Ver
    10
    Posts
    6

    Re: Soccer Substitution

    Yea.. I have a lot of moving parts with a starting line up/ subs, etc...

    Thanks for taking the time to reply... I'll try this out.

  10. #10
    Registered User
    Join Date
    07-01-2020
    Location
    USA
    MS-Off Ver
    10
    Posts
    6

    Re: Soccer Substitution

    [QUOTE=JeteMc;5359263]I don't really understand how you are recording the information on the Summary sheet as there isn't a list of names to match those on the Dummy Player Stat sheet.
    That said the following may help to get the time played.
    Paste the following into cell A25 on the Summary sheet and copy down to A27 to show minutes played by Lily and Choe:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    I hope that this will be helpful.[/QUOTe

    I updated the sheet. Sorry about that.

    I am interested in the Dummy Sheet's Minutes Played item.

    It is a bit difficult to cut out a chunk of my workbook and try to send it to explain, but alot of this data compiles to give graphical visuals and compiles data for individual players through out the season.

+ 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. Question - Substitution function is substitution its own values
    By debrad1207 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-01-2018, 10:23 AM
  2. [SOLVED] Need help on substitution worksheet
    By Ereman82 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-13-2016, 10:39 AM
  3. Rank with Substitution
    By Ikrananka in forum Excel General
    Replies: 1
    Last Post: 08-25-2011, 08:33 PM
  4. Excel 2007 : Argument substitution in SUM
    By xj750rj in forum Excel General
    Replies: 2
    Last Post: 12-24-2010, 05:08 AM
  5. If then substitution
    By knitterkuba in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 12-13-2010, 05:24 AM
  6. [SOLVED] substitution
    By Cossloffe in forum Excel General
    Replies: 1
    Last Post: 06-04-2006, 02:10 AM
  7. Substitution
    By Boenerge in forum Excel General
    Replies: 2
    Last Post: 05-23-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