Forum Discussion
How to create a list of units to show only those moving in the next 90 days?
I have a huge Excel list (I called Master Tracker; see screenshot) of units scheduled to move on a given date. I am using Excel 365. The list will capture one year ago from today and one year in the future from today. My master tracker is updated constantly as we add more units every month.
Master Tracker
Monthly, I create a roll-up report for my boss which I call the "Baseball Card". I use a PowerPoint slide (see screenshot attached) to show only the units scheduled to move within the next 90 days but, I have been doing this manually and using the Master Tracker as my reference. The yellow portion in column B in the Master Tracker shows already the units that are within the 90-day window from "today" (5AUG24).
PowerPoint Baseball card
Tired of doing manual calculations, I took the Excel Master Tracker and created a second tab called Baseball Card (see screenshot attached). The purpose of this tab is to substitute the PowerPoint presentation (Baseball card) done manually to reproduce all my data by using Excel formulas as much as possible.
Excel Baseball card
I am doing well so far but the challenge I have now is to come up with a way to display a list (rows will vary depending on how many units) of "Forces projected next 90 days". This list must display only the units (By MSC, by unit, and by country) within the next 90 days from today. I thought a Pivot table would do the trick, but I already tried and couldn't figure it out. I really would appreciate any help on this.
3 Replies
- Rodrigo_Iron Contributor
kharacol
I did a small sample file based on the image you've provided. Assumed that your column B in master tracker sheet has a formula of '=Column F-Today()'in baseball card sheet, I used filter function to return the selected arrays based on the dates that are projected for the next 90 days master tracker sheet. '>0, <=90'
=FILTER( CHOOSE({1,2,3}, Table1[MSC], Table1[Unit], Table1[Country] ), (Table1[Days from HSAD]>0) * (Table1[Days from HSAD]<=90) )and inserted the 'total units' on the title of the table
="Forces Projected next 90 days " & "Total Units: " & ROWS(FILTER(CHOOSE({1,2,3}, Table1[MSC], Table1[Unit], Table1[Country]), (Table1[Days from HSAD]>0) * (Table1[Days from HSAD]<=90)))
to convert/export the baseball card sheet to PowerPoint. you need to use a macro.Sub ExportToPowerPoint() Dim pptA As Object Dim pptP As Object Dim pptS As Object Dim ws As Worksheet Dim rng As Range Dim lastCell As Range Dim img As Object Set ws = ThisWorkbook.Sheets("Baseball_Card") Set lastCell = ws.Cells.SpecialCells(xlCellTypeLastCell) Set rng = ws.Range("A1", lastCell) rng.CopyPicture Appearance:=xlScreen, Format:=xlPicture On Error Resume Next Set pptA = GetObject(class:="PowerPoint.Application") If pptA Is Nothing Then Set pptA = CreateObject(class:="PowerPoint.Application") End If On Error GoTo 0 Set pptP = pptA.Presentations.Add Set pptS = pptP.Slides.Add(1, 11) ' 11 = ppLayoutTitleOnly pptS.Shapes.PasteSpecial(DataType:=2).Select ' 2 = ppPasteEnhancedMetafile Set img = pptA.ActiveWindow.Selection.ShapeRange With img .LockAspectRatio = msoFalse .Left = 0 .Top = 0 .Width = pptP.PageSetup.SlideWidth .Height = pptP.PageSetup.SlideHeight End With pptA.Visible = True Set pptS = Nothing Set pptP = Nothing Set pptA = Nothing End Subthe macro will find the last cell with value (bottom-right) in order to set a range to convert and copy the sheet content into an image and will paste it in PowerPoint.
sample file > @kharacol.xlsx - Rodrigo_Iron Contributorwould you mind uploading your sample data here or thru cloud/drive (removing/replacing some sensitive data)
- kharacolCopper Contributor
I have been trying to share the Excel documents but I cannot transfer it to my personal account. It looks like it has an inherent encryption that doesn't allow me to open it on my personal computer since I created the document from a government account. Let me know What you need to know or I can try to re-create the tracker in my personal account.