Use of data from drop down menu and date ranges for pivot table

Hello, I'm new on the forum and appreciate the help. I created a weekly marketing count report in Excel 2004 (Mac) that counts new prospects sorted by marketing source (where they heard about us). The marketing sources are on a drop down menu to make it quick and easy for our staff members to add a new prospect. The final weekly report is located on the original worksheet. This report works and everyone likes it. I attached my Excel spreadsheet for your review.

However, I am thinking ahead and I want to change this report so it functions as a working, growing database. I don't want new prospects separated by weekly reports. I want them all on one growing database. I need to extract weekly reports from this growing database.

I am not proficient in Excel, so I apologize if my questions are naive. I need a weekly report that gives me a count per marketing source in one specific week.
1. Every formula or method that I researched to create a pivot table for a weekly report always seems to involve a finite range of cells, such as counting data from A1 to A25. If my staff members keep adding new data, how can I use a finite range of cells in a formula?
2. How do I pull data in one specific week?
3. How do I transfer my countif formulas that count prospects by marketing source to the new pivot table?

Thank you.