Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #16  
Old 03-27-2020, 12:32 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: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Moving columns around is really simple when using defined Tables!

Select whole column (click on column label at top of page, eg. G for DueDate column);
Holding down Shift key, place mouse cursor over either of column left or right borders (crossed arrows are displayed), press down left key, and drag the column to new location (formula(s) in moved column remain unchanged);
Release mouse key and Shift key.

To add new column (e.g. SentForReview), simply insert a new column into wanted position, and then edit column header. After that:
1. you can enter formula into new column, when this is meant for this column;


2. you can enter values into new column, when this is meant for this column;
3. you can edit existing formulas in other columns to take into account added column.

I moved DueDate and added SentForReview columns. And I edited formulas in other columns so they can cope with empty rows in Table.
Attached Files
File Type: xlsx DueDate-test (1).xlsx (32.5 KB, 7 views)
Reply With Quote
  #17  
Old 04-06-2020, 07:59 AM
jthomas666 jthomas666 is offline Due Date Summary based on multi-sheet spreadsheet Windows 10 Due Date Summary based on multi-sheet spreadsheet Office 2016
Competent Performer
Due Date Summary based on multi-sheet spreadsheet
 
Join Date: Jun 2016
Location: Warner Robins, GA
Posts: 171
jthomas666 will become famous soon enough
Default

Again, made it over a week this time!

I've added a new worksheet, DIREPs. This sheet will track a different set of objects, but using the same base calculations. I got the dropdown to select the doctype set up correctly, but the calculations don't seem to be working.
Attached Files
File Type: xlsx DueDate.xlsx (37.1 KB, 5 views)

Last edited by jthomas666; 04-06-2020 at 08:56 AM. Reason: forgot attachment
Reply With Quote
  #18  
Old 04-06-2020, 10:17 PM
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: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

From your example and from formulas on sheet DIREPs, it looks like DIREP is additional characteristic of document - parallel with DocClass. When this is the case, then you don't need additional sheet, but you need additional columns for DIREP indo in documents table.

At same time, from you having DIREP types listed in dropdown of DocClass, this assumes DIREP type are another document classes like release, training, etc. When this is the case, then a document can have only one of them selected - this means you need additional helper columns after column DCM in documents table. And again - no need for separate DIREP table.
Reply With Quote
  #19  
Old 04-07-2020, 06:08 AM
jthomas666 jthomas666 is offline Due Date Summary based on multi-sheet spreadsheet Windows 10 Due Date Summary based on multi-sheet spreadsheet Office 2016
Competent Performer
Due Date Summary based on multi-sheet spreadsheet
 
Join Date: Jun 2016
Location: Warner Robins, GA
Posts: 171
jthomas666 will become famous soon enough
Default

Actually, DIREPs are software fixes, not documents. There is some overlap with the documents in terms of the tracking information needed.

I might be able to fit the direps into the document sheet...I'll see what I can come up with.
Reply With Quote
  #20  
Old 05-13-2020, 11:12 AM
jthomas666 jthomas666 is offline Due Date Summary based on multi-sheet spreadsheet Windows 10 Due Date Summary based on multi-sheet spreadsheet Office 2016
Competent Performer
Due Date Summary based on multi-sheet spreadsheet
 
Join Date: Jun 2016
Location: Warner Robins, GA
Posts: 171
jthomas666 will become famous soon enough
Default

sigh. I am missing something very basic on how this spreadsheet calculates dates.

trying to calculate Due Dates for specific project documents:

CDRLs=SRS/STP
DocClass=Projects
Due Types Calendar Day (CD)

Should be very simple--Base Date = 30

not working. I can't figure out if the problem is with the DueType or if it's somewhere in the helper columns.

Suggestions?

As always, thank you so much for your assistance.
Jim
Attached Files
File Type: xlsx DueDate.xlsx (40.6 KB, 5 views)
Reply With Quote
  #21  
Old 05-14-2020, 05:08 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: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Due Date is empty!
Reply With Quote
  #22  
Old 05-14-2020, 08:00 AM
jthomas666 jthomas666 is offline Due Date Summary based on multi-sheet spreadsheet Windows 10 Due Date Summary based on multi-sheet spreadsheet Office 2016
Competent Performer
Due Date Summary based on multi-sheet spreadsheet
 
Join Date: Jun 2016
Location: Warner Robins, GA
Posts: 171
jthomas666 will become famous soon enough
Default

Quote:
Originally Posted by ArviLaanemets View Post
Due Date is empty!
That's the problem--It should have been calculated (as June 11, 2020) when I entered the Base Date.
Reply With Quote
  #23  
Old 05-15-2020, 03:13 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: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

In attached workbook are column names switched for couple of columns formulas did refer to wrong column), and the formula in one column is corrected
Attached Files
File Type: xlsx DueDate.xlsx (40.1 KB, 6 views)
Reply With Quote
  #24  
Old 05-15-2020, 08:36 AM
jthomas666 jthomas666 is offline Due Date Summary based on multi-sheet spreadsheet Windows 10 Due Date Summary based on multi-sheet spreadsheet Office 2016
Competent Performer
Due Date Summary based on multi-sheet spreadsheet
 
Join Date: Jun 2016
Location: Warner Robins, GA
Posts: 171
jthomas666 will become famous soon enough
Default

There appear to be some issues with the new file:

1. Column P4--p26 have the wrong values. Not sure what's going on here.
2. The due date for projects is still not calculating. (see row 32 for an example)

thanks,
jim
Reply With Quote
  #25  
Old 05-15-2020, 10:01 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: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

I don't think I can help much currently. My hand and shoulder are hurting over 5 weeks for now, and typing anything or clicking mouse is a lot of pain.
Reply With Quote
  #26  
Old 05-15-2020, 11:44 AM
jthomas666 jthomas666 is offline Due Date Summary based on multi-sheet spreadsheet Windows 10 Due Date Summary based on multi-sheet spreadsheet Office 2016
Competent Performer
Due Date Summary based on multi-sheet spreadsheet
 
Join Date: Jun 2016
Location: Warner Robins, GA
Posts: 171
jthomas666 will become famous soon enough
Default

Quote:
Originally Posted by ArviLaanemets View Post
I don't think I can help much currently. My hand and shoulder are hurting over 5 weeks for now, and typing anything or clicking mouse is a lot of pain.
No worries. I can brute force the calculation, as it's the simplest one.

Take care of yourself!
Reply With Quote
Reply

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 09:16 AM.


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