Revenue/Income Summary

[camaro03]

I own a local restaurant and want to streamline my excel spreadsheets. Currently, I have yearly workbooks (2005, 2006, 2007, etc.) that have all of my revenues included in them, split up monthly by worksheets. I then have lines for each day within the month for the revenues earned at that day.

So, if I want to find how much revenue I earned on March 1st, 2006, I go to my 2006 book, March worksheet, and look at the 1st. Here's where my problem lies.

I want to create a new spreadsheet where all I have to do is type in a certain date (say March 1st, 2006) and all of the information for the date will be automatically displayed on the new sheet.

My goal is to make a spreadsheet where I can put in a date, two, or three and get some summary information!

[Ron Coderre]

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.

[camaro03]

Ok, is there anyway that I can use a lookup function within a workbook to find a specific worksheet. It works best for me, for simplicity, to keep the months seperate in different sheets (it makes it almost dumb proof for my other employees that may enter data).

Is there anyway to create something (macro, etc.) that could pull the value of cell A1 from sheet 1 from book1 when I enter a specific value in A1 from book2, and then when I enter a different value in A1 in book2, would get the value from A1 from sheet 2 from book1?

[oldchippy]

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.

[camaro03]

I appreciate the help...

Would there be anyway (this is really want I want) to put a certain date into cell a1 in a workbook, and have that pull information from where the data is actually located?

Again, I appreciate the help.

[oldchippy]

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.

[Ron Coderre]

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.

[camaro03]

I currently have 3 years of data in workbooks titled 2005, 2006, and 2007. The data within the books are split up into 12 worksheets (jan., feb., march, etc.). Each worksheet contains around 30 lines (for each date) and about 10 columns of information (Date, lunch sales, dinner sales, total sales, per person average, etc.). It's a relatively noncomplex sheet.

In the summary sheet, I want to put in a date (March 1, 2006) and have that sheet automatically pull the entire row from the same date in the referenced workbook, or just a cell (total sales).

[Ron Coderre]

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.