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

samnz

New member
Joined
Nov 21, 2013
Messages
7
Reaction score
0
Points
0
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 BoysWed
14-Aug7 pmvDilworthHomeField 7Quarterfinal23v8win


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 BoysSat
17-Aug7 pmvDilworthHomeField 7Final23v8win


2nd XV BoysSat
17-Aug8 pmvDilworthDilworthField 7Finalv


Netball
Premier Girls
Sat
17-Aug9 pmvAuckland GrammarAuckland GrammarCourt 9
Semifinalv


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
 

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
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?
 

samnz

New member
Joined
Nov 21, 2013
Messages
7
Reaction score
0
Points
0
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.
 

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
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
 

samnz

New member
Joined
Nov 21, 2013
Messages
7
Reaction score
0
Points
0
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
 

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
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 :)
 

Attachments

  • IndexFooball.xlsm
    31.5 KB · Views: 57
Last edited:

samnz

New member
Joined
Nov 21, 2013
Messages
7
Reaction score
0
Points
0
Awesome!!! Thank you so much for all your effort. I'm going through it now and looks fantastic
 

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
2010
Glad to hear from you. When I saw the cross posts, I thought that you must have found a solution elsewhere. That often happens :)
If you need any help getting it "off the ground", you know where to find me.

regards

Hercules
 
Top