Revenue/Income Summary

Without knowing your data structure, we probably can only suggest generic approaches.

However, since you have a need to calculate values for ranges of dates from specific months, I'd suggest transforming your data this way (if you can).

Put ALL of the months in one sheet with appropriate column headings. Start with January and append the subsequent months below. Once that is done, you have the equivalent of a data table that you can use as the basis for lookup functions, queries, and pivot tables....Plus you'd then be able to calculate values for multiple months, weeks, month-to-month comparisons, etc.

I hope that helps.

Here's another approach

Making a Summary workbook

Open your summary workbook and your 2005 workbook. Now what data do you need? For now you just need information from the March worksheet in the 2005 workbook.

Go to the March worksheet and pick a cell with the title say.

Go to your Summary worksheet, select a cell, press = and then click in the title cell of your March worksheet and press Enter.

You should now have the title in your summary worksheet and a formula something like

=[March.xls]!$A$1

Your can repeat this for what ever information you require on your Summary workbook sheet. Any information in your yearly workbooks that are linked to these cells will be updated if the information changes in the yearly workbooks.

Close your March workbook.

Now you have your summary information, but you would like to “drill” down to see the detail.

The way to get this to work is to go to Tools > Options > Edit and uncheck "Edit directly in cell".

When you double-click one of your linked cells in your Summary workbook that references a cell in another sheet or another file, it opens that file and you have all the information at hand.

Give us as much information as you can about how you want to use the data from the source workbook.

Will you want to calculate ranges of dates? or data from specific dates?
What volume of information will you be pulling from that workbook? If it's only a few dozen cells, then a formula approach will probably be acceptable. But, if it's thousands of cells, the kinds of formulas you'd need could result in an intolerably sluggish workbook where a pivot table or vba driven solution would be quicker.

If you have a date on your March spreadsheet, link that to your Summary worksheet, then when you double-click on that date it will open the file.

OK....I think I have enough information to give you some ideas.....

You don't have much volume of data for any given month, so here's a thought:

Hopefully, each month has the same structure of column headings with dates from 1 through the last day of the month listed in sequential order and all beginning in the same cell of each worksheet.

Open both workbooks: Summary and 2006.

1) Using the Summary Workbook:
2) Copy the column headings from one of the 2006 source sheets into the summary sheet....starting in cell A1

3) Select cell A2
4) Type =......then select cell A2 of the January sheet in the source book....an press [ENTER]
That will create a formula like:
=[2006_Sales.xls]January!$A$2

5) Change the absolute references to relative references by removing the dollar signs:
=[2005_Sales.xls]January!A2

6) Copy that formula across to the right and down 31 rows.

That should result in a formula driven "copy" of the January sheet of the source workbook.

7) If you want to see the March data....
From the Excel Main Menu:
<edit><replace>
Find what: January
Replace with: March
Click the [Replace All] button

Note: you may need to click the [Options] button and
set Look In to "Formulas"

Now, all of the formulas reference March

If you want to reference the 2005 workbook....
Find what: 2006
Replace with: 2005
Click the [Replace All] button

Is that something you can work with?

If yes....then maybe you'd be interested in using some simple macros to make some of that easier.

I think I have something you can work with.

I attached a zip file containing 3 files:
2005_Sales.xls......contains dummy data for Jan and Feb of 2005
2006_Sales.xls......contains dummy data for Jan and Feb of 2006
Summary.xls

1) Extract all 3 files to the SAME folder on your computer.
2) Open all 3 files
3) Select the Summary.xls workbook

Sheet1 of that workbook has a simple demo.
You enter the Year, Month, and Day for the data you want to see and the
formulas to the right of those inputs will read that data from the appropriate
workbook and worksheet
.....BUT, here's the minor catch:
The referenced workbook needs to be OPEN.

So, if you enter 2005,2,1 in the first three input cells
and 2005,1,1 in the second three input cells....
the formulas will compare 01-FEB-2005 to 01-JAN-2005.


Is that an approach you'd be interested in?

Thanks so much for letting us know we helped!

I suspect there will be some kind of flaw in whatever method you use to calculate the same weekday of the previous year.

I went with this one, which is based on the WeekNum of the target date:

Please Login or Register  to view this content.

NOTE: You'll need the Analysis ToolPak add-in engaged for the WEEKNUM function to be available.

Does that help?