Your current design means, that whenever you add a new class of documents, you have to add a new worksheet, and redesign your reports.



Better design is, where all your main data entry (document register) is in single table, with additional tables to define document classes and calculation rules. Then when you need to add a new document class, you simply define it, and the you can immediately register the document of new class without any changes in design of your application.

Added is an example workbook which illustrates this approach.

Document classes are defined in table on sheet DocClasses. Calculation rules for every class are determined by TypeCode.

TypeCodes are defined in table on sheet DueTypes.

In table on sheet Documents, columns with differently colored headers contain formulas. Columns with dark green headers are meant to display info for users. Columns with brown headers are helper columns used for calculations, and may be set hidden.
When document is delivered, it may be delivered On Time, or not On Time (Overtime). When document is not delivered jet, it may be Overtime (OnTime= FALSE), or it's due date is later than today.

At least most of reporting you wanted, you can get simply using autofilter on documents table. You also can create any number of different report sheets - when all data is in single table, then this is much easier. When you need some report you aren't able to figure out, post here what you want.

You can freely rename worksheets, Table headers, and defined Tables - all formulas in workbook will be updated automatically.

Attached Files

DueDate.xlsx (17.0 KB, 31 views)

Good Lord, that's incredible!

Thank you so much!

Also, could you point me to a document that goes over how to set this kind of stuff up?

Thanks again!

What do you mean by it?

I have used defined Tables in my example. You can define a table as Table, selecting any field in regular excel table (which must have column headers btw.), and then clicking Table in INSERT menu. You can name any created Table with unique Name after that.

Defining a Table enables DESIGN menu whenever any table element (field, column, row, header, etc.) is selected.

In defined Table, you can use specific Table syntax for all Excel formulas, and anywhere in Excel workbook you can refer to specific Table elements in formulas (in same Table, or in any Table in your workbook). E.g. when you have defined Table and named it tMyTable, then you can:

1. refer to whole datarange of your Table, like
2. refer to whole column in datarange of your table, like
3. refer to cell in any column of tMyTable in same row as your formula is placed, like
etc. (When you refer to some element in same Table, you can omit Table name.)

About DueDate calculation:
In my example, there was only 2 calculation rules (2 due types). I implemented a formula, where for every different due type due date is calculated (= DueDate1 + DueDate2 + ...) , and it may have value 0 (i.e. this due type is not applied) or value > 0 (i.e. this due type is applied and conditions for calculation are filled). Those due dates for every due type are then summarised. For every entry in table, only one of those partial dates may have value > 0. As result, the formula returns 0 when no condition for due date calculation is filled, or it returns a due date for due type which can be applied.
When you have more than 2 due types, then probably it will be best to have a helper column for every due type separately. Then DueDate is calculated like
Partial formolas for both due types in my example are
To have initial due date calculation(s) in helper column(s) is because date 0 in Excel equals with January 1st 1900, but you need an empty string instead. When you have a long formula 2 times in same cell, then it will be a bit messy.

Of course you can make a next step and use dynamic names instead of invisible helper columns, but this takes the complexity to entirely new level.