Page 1 of 2 1 2 LastLast
Results 1 to 10 of 13

Thread: How to pull entire row data from 1 sheet to another ONLY if cell in row is this week

  1. #1

    Question How to pull entire row data from 1 sheet to another ONLY if cell in row is this week



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Creating a Sport Draws & Results spreadsheet.

    The first sheet is "This Week" while following sheets are "Netball", "Football" etc. In a sport sheet there are rows of data that contain the team, date, time, opposition, location, and results. See below:

    1st XV Boys Wed
    14-Aug 7 pm v Dilworth Home Field 7 Quarterfinal 23 v 8 win


    There would be a large number of rows in a sport sheet as would contain all the teams within that sport, and all the games for that year.


    How can I pull content from a sport sheet into "This Week" sheet, ONLY if the date is within the current week?


    So "This Week" sheet would be a list of all teams and all sports playing in the current week...

    Football
    1st XV Boys Sat
    17-Aug 7 pm v Dilworth Home Field 7 Final 23 v 8 win


    2nd XV Boys Sat
    17-Aug 8 pm v Dilworth Dilworth Field 7 Final v


    Netball
    Premier Girls
    Sat
    17-Aug 9 pm v Auckland Grammar Auckland Grammar Court 9
    Semifinal v


    Senior A Girls


    Happy for there to be no content sitting alongside a Teams name in "This Week" if there is no row with the current week. i.e. Senior A Girls under Netball left blank

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    772
    Articles
    0
    Excel Version
    2010
    Hello and welcome to the forum.
    1. If it was possible to include the sport name in the sport sheet data instead of needing a separate section in "This Week", the solution would be more robust.
    2.What is the time frame that needs to be regarded as "This Week" rather than next week? Could I perhaps want details about Sat 17 August on Fri 9 August ?
    3. Can games be played on any day or just Sat ? If I enquire on Thurs should I see a game played earlier in the week?

  3. #3
    1. Yes the sport name/discipline and sport team name can be in the sport sheet and this week sheet.
    2. This week would be Monday - Sunday. If there was a need, it could possibly be This Week and Next, so display each teams games for the current and following week.
    3. Games can be played on any day of the week as multiple sport disciplines, or even two games a week rather than one (minority).
    People will always be able to go to a sport sheet ie. Netball and see all the sport draws and results for every team and every game for the season.

  4. #4
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    772
    Articles
    0
    Excel Version
    2010
    Hell Samnz
    I just wanted to let you know that Im working on your problem which is proving quite challenging. Obviously if you solve this elsewhere I would be grateful if you could let me know.
    Two additional questions:
    1. I am right in my understanding that the Sport Sheets that you want to extract from are in the plural i.e you have a separate sheet for each sport ?
    2. The column structure for each Sport Sheet is the same ? If they are not its a bit of a show-stopper!

    Will keep you informed

    Hercules

  5. #5
    Quote Originally Posted by Hercules1946 View Post
    1. I am right in my understanding that the Sport Sheets that you want to extract from are in the plural i.e you have a separate sheet for each sport ?
    2. The column structure for each Sport Sheet is the same ? If they are not its a bit of a show-stopper!
    1. yes and 2. yes

    I've just tried including a link to the document, but apparently my post count needs to be higher... might have to cheat the system for that

  6. #6

  7. #7

  8. #8
    The beginnings of the document can be found here
    https://www.dropbox.com/s/ehazfa674p...20Results.xlsx

    At the moment, the data entry is fake but it does include everything will be needing

  9. #9
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    772
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by samnz View Post
    The beginnings of the document can be found here
    https://www.dropbox.com/s/ehazfa674p...20Results.xlsx

    At the moment, the data entry is fake but it does include everything will be needing
    Thanks for the response - leave it with me and I get back to you shortly

  10. #10
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    772
    Articles
    0
    Excel Version
    2010
    Hello Samnz
    I have now got your spreadsheet to the point where its doing what you requested originally. Ive looked at what you have posted on the drop box, and it isn't a lot different
    from what Id envisaged. I think where we are is that Im providing the functionality that you wanted but not providing for the formatting and merged cells etc in yours.
    I haven't provided for details from more than one sport in a single This Week, because it would be tricky setting the correct formulae not knowing how many records will be
    pulled in for each sport.

    Heres what it does:
    1. Theres a list of W/c Dates to pick, and a list of sports to pick from, and it will return the relevant rows from the sport selected.
    2. You can create further sports by adding the sheets and amending their names into the selection list.
    3. If you want to reset the dates just edit that list.
    4. If you need more entries on the extract, you can copy the formulae down the sheet, but do read comments about the array formulae.
    5. A named range takes care of keeping up with new records so you dont need to worry about adjusting for that.
    6 To speed up processing Ive added a custom function that reduces formula complexity.

    Technical:
    In 'This Week', Cells in columns T and U and in Row 2 From V to AH are used to support processing.
    Named Range lr has been set up to calculate the number of rows in the sports sheets. To be counted
    a new line must have a date. If you are using a different sheet the formula will need to be added to a
    new entry with the same name.
    A user defined function has been added as a standard VBA module, so if your going to work with a different
    book , you'll need to copy it into a standard module.
    The formula in This Week col B are array ones. THey can be copied and pasted normally once established,
    but if you click one in the formula bar, of hit F2 Excel withdraws the array status anticipating an amendment.
    To restore the status press CTRL+SHIFT+ENTER before leaving edit mode. Excel places { } brace characters
    at either end of the formula to restore array status.
    This applies to This Week Col B only. Remember if your just copying the braces are copied as well, so that works
    as with none-array ones.
    This is all incorporated in the attached workbook. Have a look and let me know what you think.

    regards
    Hercules
    Attached Files Attached Files
    Last edited by Hercules1946; 2013-11-25 at 04:08 PM.

Page 1 of 2 1 2 LastLast

Tags for this Thread

Posting Permissions

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