How to pull entire row data from 1 sheet to another ONLY if cell in row is this week
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
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?
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.
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
1. yes and 2. yesOriginally Posted by Hercules1946
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
post 1
post 2
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
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
Last edited by Hercules1946; 2013-11-25 at 05:08 PM.
Posting Permissions
Reply With Quote
Bookmarks