Forum Discussion
Formulas
Your description is inadequate for anybody to offer help, which is no doubt the reason you've had no replies.
Is it possible to upload a sample of your worksheet, devoid of any real names or other confidential/private information?
I can be fairly confident in telling you, even without seeing it, that you'll probably be asked why you keep separate sheets for each week. In general, Excel works well when a single table is at the heart of it, with dates (or week numbers) as a column to differentiate between entries. You might be able to produce monthly or annual summaries very easily. The specific "How-to" might vary depending on specifically what it is you're recording, but I want you to be aware that there may well be a suggestion that you re-think how you organize the workbook.
But the first step will be to see what you're working with.
Here is a sample of our report. Trying to figure out on the formula for the worksheet in the Monthly. To get rid of duplicate entries.
My, oh my. That is an ambitious workbook. I'm amazed that you have only the one question.
Looking through fairly quickly, it looks to me as if there are a number of formulas that require regular updating as you move from month to month. A formula like this, for example:
=SUM('Week of November 23 2019'!L32:M33)
What happens when you move to December's accounting? And then the next year?
And that just scratches the surface.
From where I sit, and I'm sorry to say it, fixing that one formula--the one you asked about--would be tantamount to putting a bandage on a deeper wound. It might give some temporary relief, but you really need more major treatment.
My honest recommendation, if you are determined to stay with Excel, would be to
- research Power Query, with which I'm only barely familiar (it has come along in Excel's arsenal since I retired), OR
- research the various database functions that have been around for a few decades in Excel. Such functions as DSUM, DGET are functions I've used in creating summary reports such as your output documents, OR
- research the use of Microsoft Word's mailmerge for your weekly and monthly reports, using Excel as the datasource
NOW...to implement any of those suggestions would require you to change the way you assemble your "raw data" into a single Excel Table, and that itself would be a major job. Once done, however, you'd have a far more smoothly functioning system.
What you have at present could serve as a classic example (this is my hypothesis) of a process that worked well on paper, back a number of years now, and was moved over to Excel when it still was simpler, and took advantage of Excel's abilities to add designated cells up accurately, but DID NOT take advantage of some of Excel's far more powerful functions for summarizing data from a well constructed table (functions such as those I've mentioned above). The clue to that hypothesis is that your data collection sheets all look pretty (nice colored rows, layouts that look like they were copied from old paper tally sheets for each day's sales), but layouts that are not at all capable of supporting DSUM, DGET, Pivot Tables, Power Pivot, or Power Query...and some combination of those tools would serve you far better--in the long run--than what you have.
The other recommendation, frankly, since it looks like you have a pretty lively business here, is to move your accounting to a software package designed specifically for this kind of thing. QuickBooks comes to mind, but I'm sure there are others.
This was a form that the Eagles Trustee transfer to Excel. We have to input our numbers in every day, week then transfer to the weekly. On the Weekly, we input our numbers in on what is being asked. Then those numbers get transfer to the Monthly.
