dusty_j
dusty_j

Reputation: 51

excel indirect function to read dates and return dynamic values

looking through the forum and can't find what I need. I have 100+ sheets with unique sheet names and data in column B. Column B will contain various END DATES. some sheets will have 1 or 2 end dates, others have upwards of 30 end dates. . I would like to create a summary page containing a table that will update itself to show all sheet names that have END dates in column B expiring within the next 30 days. is this something that requires coding? or use of the excel indirect formula with maybe a vlookup wrapped around it?

Upvotes: 0

Views: 1850

Answers (3)

jeffreyweir
jeffreyweir

Reputation: 4834

For completeness, here's how you would do this using PowerQuery/Get & Transform if you had Excel 2013 or later.

First, you would turn each of the data areas in your workbook into Excel Tables (aka ListObjects), by selecting them and either using the Insert>Table command from the ribbon, or simply by using the keyboard shortcut [Ctrl] + [T]. Ideally you would give them all a name that denotes what they are. (I've used the prefix "Input_" and then a running count, because later this 'handle' will help me to ignore any Tables that I don't want in my end result, simply by seeing if it's name is prefixed with "Input_").

Then from the Data tab, select New Query>From Other Sources>Blank Query:

enter image description here

A mysterious looking UI called the PowerQuery window will open with nothing much of interest in it. If you type =Excel.CurrentWorkbook into the formula bar and press Enter, then a list of the various Tables in your workbook will populate in that window, as shown below. And if you click that twin arrow icon to the right of the Content column, a menu will appear that lets you expand those tables to include the actual columns in each of them, and bring them into the PowerQuery window:

enter image description here

And here's how that data dump looks, when you push OK:

enter image description here

This next bit is only required if you have other Tables in your workbook already that you don't want to show in your end mashup. In the screenshot below, I'm filtering the result set to only include tables with the Input_ prefix:

enter image description here

enter image description here

And then I'm going to change the data type of that DateTime column to a simple Date, by selecting the column concerned, and then choosing Data Type>Date from the Transform tab.

enter image description here

Then I'm going to select Close and Load To from the menu at top right:

enter image description here

..and then select the "Only Create Connection" and "Add to Data Model" options from the resulting dialog:

enter image description here

Now back in Excel, I create a PivotTable, and leave the "Use this workbook's Data Model" option checked:

enter image description here

...which brings up a slightly different looking PivotTable Fields List than you usually see:

enter image description here

I now add the Name (Table Name) and End Date fields to my Pivot, and set a date filter to just show dates within the next 30 days:

enter image description here

enter image description here

enter image description here

And here's the end result, a PivotTable that shows just those tables with end dates within the next 30 days:

enter image description here

The beauty of this approach is that if you later add more tabs with more input tables, then provided you prefix them with "Input_" then they will automatically appear next time you refresh the PivotTable. And furthermore, instead of having a report that merely tells you which tabs have applicable End Dates, the PivotTable also tells you which individual records in those tabs are involved.

There's more stuff I would do along the way that I haven't shown, such as give the columns friendlier names by ditching the "Content." suffix, and I'd probably write a simple macro to automatically refilter the PivotTable based on 30 days from the current date. But this at least shows you the benefits of upgrading to Excel 2013 and using PowerPivot/Get and Transform to radically automate tasks such as this.

Upvotes: 2

ImaginaryHuman072889
ImaginaryHuman072889

Reputation: 5205

Neither VLOOKUP nor INDIRECT are required for this problem.

Assuming your dates in the B columns are in Excel's serial number format, this formula will calculate the amount of cells in the B column of Sheet2 that are within 30 days from today's date:

= SUMPRODUCT((Sheet2!B:B>=TODAY())+0,(Sheet2!B:B<=(TODAY()+30))+0)

You can just duplicate this formula for however many sheets you need, e.g.

= SUMPRODUCT((Sheet2!B:B...))+SUMPRODUCT((Sheet3!B:B...))

EDIT

Per @Jeeped's comment, it is recommended to narrow down this range (B:B) just to the end of your data. (e.g. if you have at most 30 rows of data, change to B1:B30)

Upvotes: 2

newacc2240
newacc2240

Reputation: 1425

Private Sub ChkEndDates()
    Dim ws As Worksheet
    Dim cell As Range
    Dim rowCount as Long
    For Each ws in Worksheets
        If ws.Name <> '"summary page" Then
            rowCount = WorksheetFunction.CountA(ws.Range("B:B"))
            For i = 1 To rowCount
                If (ws.Cells(i, 2).Value - Date < 30) And (ws.Cells(i, 2).Value > Date) Then
                    MsgBox ws.Name
                    'And maybe push into the summary page
                End If
            Next i
        End If
    Next ws
End Sub

Upvotes: 2

Related Questions