Populate worksheet with data that meets date range criteria

[P0llyW0G]

Goal:

By entering a beginning date and an ending date on Sheet2, I hope to
populate Sheet2 with data from Sheet1 within the selected date range below
row 3 on Sheet2 and above a row of formulas to calculate column totals.

Setup:

Sheet1 –
• A5:A1058 has a list of dates in ascending order
• Columns B thru E have the date’s respective total sales in dollars for
each inventory item (e.g. B = Bottled Beer, C = Draft Beer, D = Liquor, and E
= Wine/Champagne).

Sheet2 –
• Designed as a printable report.
• Cell B2 = Beginning Report Date and D2 = Ending Report Date
• Cells B5:E5 are waiting for data to be copied down in row 4 to total (i.e.
the formulas move just below the populated cells to give the column totals
for the inserted data.

I have tried a few suggestions from similar posts. However, none quite work
the way I need it to.

 

[ShaneDevenshire]

Hi,

I would consider putting the total calculation on the top of Sheet1 and then
applying AutoFilter to that range. The result would be similar if not
identical to what it sounds like you are trying to do. When you apply the
filter you will see only the records that meet the desired condition. If you
use SUM functions on Sheet2, then on Sheet1 you probably would want
SUBTOTAL(9,myrange). 9 tells the subtotal function to sum only visible
cells, not the ones hidden by the filter. myrange is any range you want.
Auto Filter icons don't print.

 

[P0llyW0G]

I figured out a way of doing it by modifying a couple of scripts. The filter
script came from Debra Dalgleish’s website at http://www.contextures.com. I
realize this is a bit detailed. However, I only want to save someone else
with similar Excel skills some time trying to figure it out. I’m sure there
is a hundred ways of doing populating cells, but I’m taking the gold I panned
and cashing it in. It's my first time using the Visual Basic Editor.

MAKE CHANGES TO LAYOUT FROM ORIGINAL POST:

1. Rename Sheet1 to “Dataâ€
2. Rename Sheet2 to “FilteredReportâ€

CHANGES TO “FilteredReportâ€

1. Have cells A5:A1058 reference to the “Data†worksheet (e.g. A5:
=Data!A5). I do this to keep my data entry sheet separate from my report
sheet; I find it cleaner.

2. Enter the following in “FilteredReportâ€:
E2: Date
F2: Date
E3: =â€>=†& B2
F3: =â€<=†& D2
I hide their visible values by giving E2:F3 a white font.

DEFINE NAMES FOR FILTER:

1. Go to INSERT – NAMES – DEFINE
2. Type “Alldates†in name
3. Type “=OFFSET(FilteredReport!$A$5,0,0,COUNTA(FilteredReport!$A:$A),1)†in
REFERS TO.
4. Click ADD
5. Without exiting, type “Database†in name
6. Type “=OFFSET(FilteredReport!$A$5,0,0,COUNTA(FilteredReport!$A:$A),5)†in
REFERS TO.
7. Click ADD and close

CREATING THE FILTER MACRO:

1. Insert a module in the Visual Basic editor by right-clicking the VBA
PROJECT [file name] in the upper left-hand pane and pasting the following:

Option Explicit

Sub ApplyFilter()
Dim wsDL As Worksheet
Dim wsO As Worksheet
Dim rngAD As Range
Set wsO = Sheets("FilteredReport")
Set rngAD = wsO.Range("AllDates")
'filter the list
wsO.Range("Database").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=wsO.Range("E2:F3"), Unique:=False
End Sub

Sub RemoveFilter()
On Error Resume Next
ActiveSheet.ShowAllData
End Sub

2. Create and assign two buttons with one each of the new ApplyFilter and
RemoveFilter macros.

CALCULATING THE FILTERED DATA FOR MY REPORT:

Within “FilteredReportâ€, in row 1063, I entered my SUBTOTAL formula to
calculate only the visible data. The space between my SUBTOTAL formulas and
the data range help to keep it visible after applying the filter.

You can use the SUBTOTAL function to calculate filtered data in different
ways.
The SUBTOTAL formula for my Beer column looks like this:
=SUBTOTAL(9,B6:B1059)
The 9 in this formula will sum the column. By substituting the number with
one below, you can perform the adjacent function for the range.

Calculation types:
1. Average
2: Count
3. Count (non-blanks)
4. Maximum
5. Minimum
6. Product
7. Standard Deviation (sample)
8. Standard Deviation (population)
9. Sum
10. Variance (sample)
11. Variance (population)

ADDING A POPUP CALENDAR TO SELECT DATES:

I added a calendar control for the date entry cell using a script I found at
http://www.rondebruin.nl/calendar.htm I modified it for this spreadsheet.
Here’s how to add it:

1. Within “FilteredReportâ€,select cell B2.
2. Go to INSERT – OBJECT – and select CALENDAR CONTROL 8.0. Right-click on
the “FilteredReport†tab and select View Code. Paste the following:

Private Sub Calendar1_Click()
ActiveCell.Value = CDbl(Calendar1.Value)
ActiveCell.NumberFormat = "mm/dd/yyyy"
ActiveCell.Select
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("B2,D2"), Target) Is Nothing Then
Calendar1.Left = Target.Left + Target.Width - Calendar1.Width
Calendar1.Top = Target.Top + Target.Height
Calendar1.Visible = True
' select Today's date in the Calendar
Calendar1.Value = Date
ElseIf Calendar1.Visible Then Calendar1.Visible = False
End If
End Sub

 

[P0llyW0G]

I figured out a way of doing it by modifying code. The filter code came from
Debra Dalgleish’s website at http://www.contextures.com. I realize this is a
bit detailed. However, I only want to save someone else with similar Excel
skills some time trying to figure it out. I’m sure there is a hundred ways
of doing populating cells, but I’m taking the gold I panned and cashing it
in. It's my first time using the Visual Basic Editor.

MAKE CHANGES TO LAYOUT FROM ORIGINAL POST:

1. Rename Sheet1 to “Dataâ€
2. Rename Sheet2 to “FilteredReportâ€

CHANGES TO “FilteredReportâ€

1. Have cells A5:A1058 reference to the “Data†worksheet (e.g. A5:
=Data!A5). I do this to keep my data entry sheet separate from my report
sheet; I find it cleaner.

2. Enter the following in “FilteredReportâ€:
E2: Date
F2: Date
E3: =â€>=†& B2
F3: =â€<=†& D2
I hide their visible values by giving E2:F3 a white font.

DEFINE NAMES FOR FILTER:

1. Go to INSERT – NAMES – DEFINE
2. Type “Alldates†in name
3. Type “=OFFSET(FilteredReport!$A$5,0,0,COUNTA(FilteredReport!$A:$A),1)†in
REFERS TO.
4. Click ADD
5. Without exiting, type “Database†in name
6. Type “=OFFSET(FilteredReport!$A$5,0,0,COUNTA(FilteredReport!$A:$A),5)†in
REFERS TO.
7. Click ADD and close

CREATING THE FILTER MACRO:

1. Insert a module in the Visual Basic editor by right-clicking the VBA
PROJECT [file name] in the upper left-hand pane and pasting the following:

Option Explicit

Sub ApplyFilter()
Dim wsDL As Worksheet
Dim wsO As Worksheet
Dim rngAD As Range
Set wsO = Sheets("FilteredReport")
Set rngAD = wsO.Range("AllDates")
'filter the list
wsO.Range("Database").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=wsO.Range("E2:F3"), Unique:=False
End Sub

Sub RemoveFilter()
On Error Resume Next
ActiveSheet.ShowAllData
End Sub

2. Create and assign two buttons with one each of the new ApplyFilter and
RemoveFilter macros.

CALCULATING THE FILTERED DATA FOR MY REPORT:

Within “FilteredReportâ€, in row 1063, I entered my SUBTOTAL formula to
calculate only the visible data. The space between my SUBTOTAL formulas and
the data range help to keep it visible after applying the filter.

You can use the SUBTOTAL function to calculate filtered data in different
ways.
The SUBTOTAL formula for my Beer column looks like this:
=SUBTOTAL(9,B6:B1059)
The 9 in this formula will sum the column. By substituting the number with
one below, you can perform the adjacent function for the range.

Calculation types:
1. Average
2: Count
3. Count (non-blanks)
4. Maximum
5. Minimum
6. Product
7. Standard Deviation (sample)
8. Standard Deviation (population)
9. Sum
10. Variance (sample)
11. Variance (population)

ADDING A POPUP CALENDAR TO SELECT DATES:

I added a calendar control for the date entry cell using a script I found at
http://www.rondebruin.nl/calendar.htm I modified it for this spreadsheet.
Here’s how to add it:

1. Within “FilteredReportâ€,select cell B2.
2. Go to INSERT – OBJECT – and select CALENDAR CONTROL 8.0. Right-click on
the “FilteredReport†tab and select View Code. Paste the following:

Private Sub Calendar1_Click()
ActiveCell.Value = CDbl(Calendar1.Value)
ActiveCell.NumberFormat = "mm/dd/yyyy"
ActiveCell.Select
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("B2,D2"), Target) Is Nothing Then
Calendar1.Left = Target.Left + Target.Width - Calendar1.Width
Calendar1.Top = Target.Top + Target.Height
Calendar1.Visible = True
' select Today's date in the Calendar
Calendar1.Value = Date
ElseIf Calendar1.Visible Then Calendar1.Visible = False
End If
End Sub