Forum Discussion

kharacol's avatar
kharacol
Copper Contributor
Aug 07, 2024

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_'s avatar
    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 Sub

    the 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_'s avatar
    Rodrigo_
    Iron Contributor
    would you mind uploading your sample data here or thru cloud/drive (removing/replacing some sensitive data)
    • kharacol's avatar
      kharacol
      Copper 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.

Resources