monthly report day by day

[NH Dan]

i'm looking to do a day/sheet report where there is a separate sheet for each day, data gets entered in as the day goes and on each sheet (day2-31) it adds up the sum of the previous days so i get a running day by day total. Is there a quick/simple way of duplicating the sheet or do i have to go into each sheet/formula and adjust the today-previous day formula links one by one ??

i've hopefully attached a copy of the workbook so you can get an idea of what i'm on about (have only done days 1-3 so far)

cheers

D

[Richard Buttrey]

Hi,

All I can say is abandon this approach before you waste any more time. You certainly don't want a sheet for every day.

You are making the same mistake that I see time and time again. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.

A lot of people start by designing the form that they expect to see as the final report or which at first glance seems the best way of capturing data, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it. Yours exhibits all those features.

You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.

So before you get too far with this I'd create a single sheet database that contains the following columns

Date
Service *
Type of Hours - * entries here will be either 'attended', 'sold', 'budget', or 'Wip'
Labour Sales
Efficiency
Hours

The * values could be Validation drop down cells from which you could pick values. The way I normally arrange these things is to have a single data entry row above the database in which the new values are entered, then a button which runs a macro that adds the new record to the database.

I can't comment about the second set of fields, vhc's...etc since it's not immediately obvious what values you expect in here, but in principle you would add these as similar columns and values to the above. Perhaps a column for vhc's which would contain either 'loaded' or 'completed' followed by another column for presumably a number. But let us see some typical values and we'll be able to advise.

[NH Dan]

I understood some of that, my working knowledge of excel is greater than my terminology knowledge so i'll have a go over the weekend and see what I come up with....

Macros are not something i'm too hot with on Excel either lol

D