Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Populate worksheet with data that meets date range criteria
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 dates 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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Populate worksheet with data that meets date range criteria
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. -- Thanks, Shane Devenshire "P0llyW0G" wrote: 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 dates 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Populate worksheet with data that meets date range criteria
I figured out a way of doing it by modifying a couple of scripts. The filter
script came from Debra Dalgleishs 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. Im sure there is a hundred ways of doing populating cells, but Im 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(Filtered Report!$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(Filtered Report!$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. Heres 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" wrote: 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 dates 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Populate worksheet with data that meets date range criteria
I figured out a way of doing it by modifying code. The filter code came from
Debra Dalgleishs 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. Im sure there is a hundred ways of doing populating cells, but Im 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(Filtered Report!$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(Filtered Report!$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. Heres 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
RANGE EXCEL copy cell that meets criteria in a range | Excel Worksheet Functions | |||
QUERY data range to populate separate worksheet? | Excel Worksheet Functions | |||
find date that meets a criteria | Excel Discussion (Misc queries) | |||
find date that meets a criteria | Excel Discussion (Misc queries) | |||
Formula to return ADDRESS of cell in range that meets criteria | Excel Worksheet Functions |