![]() |
#5
|
|||
|
|||
![]() Quote:
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 Code:
=INDEX(tMyTable,1,1) Code:
=SUM(tMyTable[ColumnHeaderValue]) Code:
=tMyTable[@ColumnHeaderValue] 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 Code:
=IF([SUM(@DueDate1], @DueDate1], ...)=0;"";[SUM(@DueDate1], @DueDate1], ...)) Code:
=([@DueType]="WD")*WORKDAY([@BaseDate];[@DueValue];tHolidays[Holiday]) Code:
= ([@DueType]="CD")*([@BaseDate]+[@DueValue]+SIGN([@DueValue])*COUNTIFS(tHolidays[Holiday];">=" & MIN([@BaseDate];[@BaseDate]+[@DueValue]);tHolidays[Holiday];"<=" & MAX([@BaseDate];[@BaseDate]+[@DueValue]);tHolidays[IsWeekend];FALSE)) 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. |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
Summriez all worksheet in summary work sheet | MILADREZAEE | Excel | 7 | 05-21-2018 05:01 AM |
How to calculate summary progress date time on summary tasks | Logotip | Project | 0 | 05-16-2017 01:18 AM |
![]() |
FenelonPaul | Excel | 5 | 09-23-2015 07:11 AM |
![]() |
nfotx | Mail Merge | 1 | 07-01-2015 12:55 AM |
![]() |
PRADEEPB270 | Excel | 1 | 11-04-2011 03:46 AM |