excel indirect function to read dates and return dynamic values

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

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)

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:

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:

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

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:

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.

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

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

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

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

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:

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

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.