Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #5  
Old 03-06-2020, 07:58 AM
ArviLaanemets ArviLaanemets is offline Due Date Summary based on multi-sheet spreadsheet Windows 8 Due Date Summary based on multi-sheet spreadsheet Office 2016
Expert
 
Join Date: May 2017
Posts: 949
ArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant future
Default

Quote:
Originally Posted by jthomas666 View Post
Also, could you point me to a document that goes over how to set this kind of stuff up?
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
Code:
=INDEX(tMyTable,1,1)
2. refer to whole column in datarange of your table, like
Code:
=SUM(tMyTable[ColumnHeaderValue])
3. refer to cell in any column of tMyTable in same row as your formula is placed, like
Code:
=tMyTable[@ColumnHeaderValue]
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
Code:
=IF([SUM(@DueDate1], @DueDate1], ...)=0;"";[SUM(@DueDate1], @DueDate1], ...))
Partial formolas for both due types in my example are
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))
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.
Reply With Quote
 

Thread Tools
Display Modes


Similar Threads
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
Due Date Summary based on multi-sheet spreadsheet Creating a dynamic summary sheet FenelonPaul Excel 5 09-23-2015 07:11 AM
Due Date Summary based on multi-sheet spreadsheet Can a Data Source be one sheet in a multi-sheet Workbook nfotx Mail Merge 1 07-01-2015 12:55 AM
Due Date Summary based on multi-sheet spreadsheet Construct a summary sheet by summing up from one or more than one sheet. PRADEEPB270 Excel 1 11-04-2011 03:46 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 08:48 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft