Attachment 21665
Quote:
Originally Posted by Pablo2000
The reason for having a separate date on each tab is these are monthly tabs and I DO NOT want to over write the data of a previous month. I want to keep each monthÂ’s data as is in that year.
|
Attached is an example, how I would do it.
All fixed values are defined on SetUp sheet as named values. In case any of those values change in the future, you have to archive the old workbook, and start a new one with new setup.
On tab Months is the Table tMonths, with columns MMText (the accounting period as text in format "YYYY MMMM", but you can use any other format if you want), the same accounting period as integer in format YYYYMM (this will make it easier to create any reports including several account periods - whenever there is a need for it in future - as those values are ordered), MMStart (start date of accounting period), MMEnd (end date of accounting period), Days (the number of days in accounting period), and RowNo (a helper column for case there will be need to get a value from this Table using INDEX(). You can predefine those periods for any time period into future, and you can add them whenever it is needed.
On tab Calendar is table tCal, with Date as 1st column, where all dates from time period the calendar is filled are present. This Table may contain any number of additional columns of data calculated based on registered date - whatever you will need. I added some needed for your current task. As this Table may be quite large, it will be reasonable to keep the data there as values. Currently I preserved columns I used to calculate those values too (columns with differently colored headers at right) - you may delete those. To avoid any problems when using Calendar in your calculations, the Calendar must start at least a month before your data entry, with a day number determined in SetUp, and must end at least with MMEnd of last registered account period in tMonths. The Calendar must contain all periods you need to enter data for, but there is no restrictions how far into future it is filled.
On tab DataEntry is table tData - obviously the table where you register all your measurement data, with some additional calculated columns which will make reporting easier. To avoid the risk to overwrite data from previous months, you can set the autofilter for this table to current accounting period (MMText). Probably it will be possible to use Data Validation for this Table to restrict entering Date values outside from selected accounting period too, but I didn't have enough free time to do this myself. The columns MMStart and MMEnd were added to make it possible when you think there is a need for this.
On tab repMM I designed a report, where at top of sheet you can select accounting period, and a summary for selected period will be displayed (it will be display the data for selected accounting period aproximately in same format as your current data entry tabs are having). I filled it with some formulas (the ones which read data from SetUp, and from Data entry table). You can fill it from rest of them easily.
When all design is done properly, this workbook may work years for you without any need of redesign (unless some cardinal changes must be done).