Calculating values based on week

[solo9999]

Hi all,

I have the following scenario:

A table that lists several rows of data for every day. For example:


trip # | trip date | number of passengers | $ paid
------------------------------------------------
123 | 01/07 | 5 | $184
------------------------------------------------
874 | 02/07 | 5 | $154
------------------------------------------------
697 | 03/07 | 5 | $151
------------------------------------------------
987 | 03/07 | 5 | $154
------------------------------------------------
988 | 04/07 | 5 | $174
------------------------------------------------
143 | 04/07 | 5 | $156
------------------------------------------------
823 | 05/07 | 5 | $177
------------------------------------------------

Every Friday, a weekly report is done for the week before

So when we are in week 32, that Friday a report is done for Week 31

The problem is that currently I have to go and manually select the dates from the week I am doing the report for and sum the data up. And I have about 25 of those sheets!

The idea is to create tables that can produce a report based on a week. A dropdown menu with weeks 1, week 2 etc. When the user selects let's say week 30 from the dropdown, Excel would do maybe a vlookup, find all the entries that belong to week 30 and sum up the $ paid for week 30. Is that possible?

!THANKS!

 

[Robert Mika]

VLOOKUP will not work accross multiple worksheet.
Try to use SUMIF with INDIRECT,
Here is a tip
http://help.wugnet.com/office/SUMIF-multiple-worksheets-ftopict1157402.html

 

[solo9999]

Thanks. The issue that I am also having is how to formulate it based on weeks. In other words, how will Excel know which dates to search for when I select let's say week 35 from the list