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