Creating Daily report forms

[DaveServo]

I need to create a daily report form for a retail store. I think I can manage it but I'm wondering if there are any existing templates and also if it's possible to have a seperate form for each month that automatically collects data from the daily forms.

[OTPM]

Hi DaveServo
You need to provide us with a little more info so we can undeerstand what you are trying to acheive and we may be able to help you then.
OTPM

[DaveServo]

Originally Posted by OTPM

Hi DaveServo
You need to provide us with a little more info so we can undeerstand what you are trying to acheive and we may be able to help you then.
OTPM

Sure,

I've seen a setup like this before. I have a spreadsheet that I use to calculate daily sales for a retail store. In the past, the guy that was doing it was actually printing the sheet and doing all the calculations manually. I've added the formulas to automate it. Anyway, also in the past, at the end of the month, the guy would take all of the sheets and manually type in all of the info into a monthly spreadsheet. I'm wondering how to make it so that, each day, after the daily sheet is filled out and then saved (with a unique filename) it could be used to populate a column on the monthly spreadsheet automatically. Make sense?

[OTPM]

Hi DaveServo
Now I understand what you are after.
Firstly you can update a summary workbook automatically. However there are certain caveats you need to bear in mind.

1. You would have to have static workbook names for the daily workbooks.
2. They need to be stored on the same network as the summary workbook.
3. You must not change the location of the daily workbooks once you have set them up and produced the for mulae in the summary workbook. If you do the automatic updates will not work.

The summary workbook will be updated automatically when a daily workbook is updated/changed. Here is the formula you need to put into the summary workbook:

=[DailyWorkbookName]SheetName!$A$1

Copy this formula to a separate cell for each daily workbook you wish to summarise.
Replace DailyWorkbookName with the correct workbook name.
Replace SheetName with the correct worksheet name.
Replace $A$1 with the correct cell reference in each daily workbook.

An easier way to create the Summary workbook formulae is to click on the cell where the Daily Total is stored and right click and select copy.
Click in the cell in the Summary Workbook where you want top store the updates and right-click and select paste link.
This will create the formula for you.
Once you have copied all the links for each daily worksheet save the Summary workbook and close it. Any updates done on the daily workbook will automatically update the summary workbook even if it is closed.

REMEMBER - DO NOT CHANGE THE LOCATION OF ANY OF THE WORKBOOKS AFTER YOU HAVE CREATED THEM.

Tony

[Catalin.B]

You can arrange your data as in sample atached; there are 2 ways:
1. in a workbook with 31 worksheets and a total worksheet,
or, if you insist on keeping a separate workbook for each day, you can use the second way:
Create a blanks folder for your templates (will contain 31 workbooks and a total workbook, linked to all 31 workbooks)
2. Copy blanks folder, rename it February 2011, insert your data; for totals, just open any time montly total workbook. This will keep the links updated for the workbooks inside this folder .
An important issue is that if you add a new row for a new product, you have to add it to all worhsheets and total worksheet. A good idea is to create your template with several blank rows, for new products, and when starting a new month, complete the blanks with last month new products, and empty rows for future new products...