Excel - pull data based on criteria

miked18

New Member
Joined
Mar 3, 2022
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Hello, I have a worksheet that tracks income and expenses. I have calculations at the top of the page for different income streams, so where the income/expenses are added are not under typical column headers at the top of the ws, lets call this ws "financials 2022". instead the headers are down around row 17. the data begins on row 19, column A to column O (this should not change in the future). What I want to do is: on a separate ws (lets call it "reports", run a "report". so either monthly or year end. after year end, financials 2022 will be archived and a new sheet "financials 2023" would be created. so for monthly, STEP 1: I would like to have some text boxes on the "reports" tab where i can select the sheet to pull the data from (options being "financials 2021", "financials 2022", etc) Q: can i have the worksheets with "financials" in the name be loaded into a dropdown via query? (there are other worksheets in the workbook with other names) i could do a table but then someone would have to update it. STEP 2 select a month. STEP 3: Hit a button that goes to the "financials" ws and looks for all entries in that date range and pulls it to the "reports" ws. Next for year end, similar to monthly, but select the "financials" sheet and it pulls add data?

Screenshot 2022-03-03 121839.png
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
12,193
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
It is hard to work with a picture. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your "financial" sheet and "Report" sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). Also, your sheet appears to contain merged cells. You should avoid merging cells because they almost always cause problems for macros. You could simply widen a column or row if you need more space or do a little research into "CenterAcrossSelection". This has the same effect as merging without actually merging any cells although it only works for horizontal ranges not vertical. You should re-design the sheets to remove the merged cells.
STEP 2 select a month.
looks for all entries in that date range
How do you select the month and date range? Please explain in detail using a few examples from your data and referring to specific cells, rows, columns and sheets.
--------------------------------------------------------
 
Last edited:

miked18

New Member
Joined
Mar 3, 2022
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Cub Pack Budget and Finances V3.xlsm
ABCDEFGHIJKLMNOP
1CUB PACK BANK BALANCEPOPCORN SUBSIDYDUESDONATIONS
2SEPT 2021-SEPT 2022START$ 2,391.03START$ -START$ -START$ -
3NUMBER OF YOUTHINCOME$ 1,505.35INCOME$ 1,357.19INCOME$ 1,200.00INCOME$ -
424EXPENSES$ 1,531.41EXPENSES$ -EXPENSES$ -EXPENSES$ -
5NUMBER OF SCOUTERSBALANCE$ 2,364.97BALANCE$ 1,357.19BALANCE$ 1,200.00BALANCE$ -
64
7PROPOSED BUDGET
8$ 7,428.00
9PROPOSED SUBSITYTOTAL MONEY
10$ 210.00AVAILABLE$ 4,922.16
11 YOUTH FEES TO COLLECT
12$ 200.00
13
14ACTIVITYPROPOSED BUDGETACTUAL EXPENSESACTUAL INCOMEDATE
15ESTIMATED COSTSUBSIDY TO USEYOUTH FEES COLLECTEDCOSTEXPENSE TYPESUBSIDY USEDACCOUNT USED FOR SUBSIDYAMOUNTINCOME TYPE
16
17
18
19Popcorn$ 2,478.00$ 1,357.19Popcorn Subsidy2021-09-01
20Apple Day
21Equipment$ 500.00
22Dues$ 1,250.00$ 1,200.00Dues2022-03-01
23Kub Kars$ 210.00$ 210.00$ 200.00
24Bank Charges$ 10.00
25Badges & Investiture$ 500.00
26Uniform Accessories$ 500.00
27Lair of the Month$ 300.00
28Year End Party
29Supplies
30Swimming
31Bowling
32Camp - Fall$ 500.00
33Camp - Winter$ 680.00
34Camp - Spring$ 500.00
35Camp - Summer
36JOTT
37All Sections Day $ 95.00Bank E-Transfer2021-10-29
38Bank Interest - Sept 21
39Bank Interest - Oct 21$ 0.35Bank Interest2021-10-01
40Bank Interest - Nov 21
41Bank Interest - Dec 21
42Bank Interest - Jan 21
43Bank Interest - Feb 21
44Bank Interest - Mar 21
45Bank Interest - Apr 21
46Bank Interest - May 21
47Bank Interest - Jun 21
48Bank Interest - Jul 21
49Bank Interest - Aug 21
50E-transfer$ 600.00Bank E-Transfer2022-01-06
51E-transfer$ 400.00Bank E-Transfer2022-01-06
52E-transfer$ 400.00Bank E-Transfer2022-01-06
53Supplies$ 65.03 Equipment/Supplies 2021-10-05
54Supplies$ 257.38 Equipment/Supplies 2021-10-05
55Supplies$ - Equipment/Supplies 2022-02-16
56Turtle Conserv Presentation$ 226.00 Activity 2021-09-18
57Brooks Family Farm$ 154.00 Activity $ 90.00Youth Fees Collected$ 90.00Youth Fees Collected2021-10-19
58Forsythe Farms$ 110.00 Activity $ 85.00Youth Fees Collected$ 85.00Youth Fees Collected2021-10-27
59E-transfer$ 5.00Bank E-Transfer2021-12-06
60E-transfer$ 5.00Bank E-Transfer2021-12-10
61Treetop Trekking$ 544.00 Activity 2021-12-04
Cub Pack Financials 2021-2022
Cell Formulas
RangeFormula
D3D3=SUMPRODUCT(SUMIF(M19:M68,TBL_BANK_DIRECT[INCOME DIRECT TO BACK ACCOUNT],L19:L68))
D4D4=G71+I71
D5,P5,M5,H5D5=D2+D3-D4
H3H3=SUMIF(M19:M69,G1,L19:L69)
H4H4=SUMIF(J19:J68,G1,I19:I68)
M3M3=SUMIF(M19:M69,L1,L19:L69)
M4M4=SUMIF(J19:J68,L1,I19:I68)
P3P3=SUMIF(M19:M69,O1,L19:L69)
P4P4=SUMIF(J19:J68,O1,I19:I68)
A8A8=C71
A10A10=D71
D10D10=D5+H5+M5+P5+S5+Y5+V5
A12A12=E71
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A59:A60Expression="if($A:$B,$A$7=""FR"")"textNO
A47:A52Expression="if($A:$B,$A$7=""FR"")"textNO
A41:A52,A59:A60Expression=B:B="sub"textNO
A41:A46,AF16:AJ17Expression="if($A:$B,$A$7=""FR"")"textNO
Cells with Data Validation
CellAllowCriteria
M19:M61List=INCOME_TABLE
J19:J61List=TBL_SUBSIDY_ACCOUNTS
H19:H55List=EXPENSES_TABLE
H56:H61List=Tables!$A$5:$A$10
 

miked18

New Member
Joined
Mar 3, 2022
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
It is hard to work with a picture. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your "financial" sheet and "Report" sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). Also, your sheet appears to contain merged cells. You should avoid merging cells because they almost always cause problems for macros. You could simply widen a column or row if you need more space or do a little research into "CenterAcrossSelection". This has the same effect as merging without actually merging any cells although it only works for horizontal ranges not vertical. You should re-design the sheets to remove the merged cells.


How do you select the month and date range? Please explain in detail using a few examples from your data and referring to specific cells, rows, columns and sheets.
--------------------------------------------------------
OK, hopefully the mini sheet i uploaded helps...
Ill look at the merged cells suggestion, thx.
For selecting a date range, I was thinking of having input boxes on a different tab ("reports" tab) where you enter the date range. lets say you want to see the activity for the month of Oct. in a cell (B2) for example (B1 would say "enter start date"). In B2 you would put Oct 1 2021.. In cell C1 it would say "enter end date" where you would put oct 31, 2021. then in cell D1 it would say "select the sheet you want to get the data from " so as in my example in my original post, you could select "financials 2021" then click a button to pull the data to the "reports" tab. the query would then goto the "financials 2021" tab. It would need to look at columns A thru O starting at row 19. it would look for all dates in column O within range (Oct 2021) and pull all data from A-O. search each row until it gets to the end.
 

miked18

New Member
Joined
Mar 3, 2022
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
I am not sure how to populate the available options for "reports" tab cell D2 where you would select the worksheet to pull data from. there would eventually be more "financials" sheets as the years progress. financials 2021, financials 2022, so on. can you query worksheet names and populate a drop down list?
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
12,193
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
Click here to download your file. In the reports sheet, first click on B1 and then E1. A calendar will pop up for you to select a start and end date. Lastly, click on H1 to select a sheet.
This macro is in the code module for ThisWorkbook:
VBA Code:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If ActiveSheet.Name Like "*financials*" Then
        If Target.CountLarge > 1 Then Exit Sub
        If Intersect(Target, ActiveSheet.Range("O16:O" & ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row)) Is Nothing Then Exit Sub
        CalendarFrm.Show
    End If
End Sub
These macros are in the code module for Sheet "reports":
VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, ActiveSheet.Range("B1,E1,H1")) Is Nothing Then Exit Sub
    Dim ws As Worksheet, val As String
    Application.ScreenUpdating = False
    Select Case Target.Column
        Case Is = 2, 5
            CalendarFrm.Show
        Case Is = 8
            For Each ws In Sheets
                If ws.Name Like "*financials*" Then
                    If val = "" Then val = ws.Name Else val = val & "," & ws.Name
                End If
            Next ws
            With Target.Validation
                .Delete
                .Add Type:=xlValidateList, Formula1:=val
            End With
    End Select
    Application.ScreenUpdating = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, ActiveSheet.Range("H1")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim ldateto As Long, ldatefrom As Long, LastRow As Long, srcWS As Worksheet
    If Target <> "" Then
        Set srcWS = Sheets(Target.Value)
        LastRow = srcWS.Range("A" & Rows.Count).End(xlUp).Row
        ldatefrom = Target.Offset(, -6)
        ldateto = Target.Offset(, -3)
        With ActiveSheet.UsedRange.Offset(3)
            .ClearContents
            .Interior.ColorIndex = xlNone
        End With
        With srcWS
            .Range("A15:O" & LastRow).AutoFilter Field:=15, Criteria1:=">=" & ldatefrom, Operator:=xlAnd, Criteria2:="<=" & ldateto
            .AutoFilter.Range.Offset(1).Copy Range("A4")
            .Range("A15").AutoFilter
        End With
    End If
    Application.ScreenUpdating = True
End Sub
 

miked18

New Member
Joined
Mar 3, 2022
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Click here to download your file. In the reports sheet, first click on B1 and then E1. A calendar will pop up for you to select a start and end date. Lastly, click on H1 to select a sheet.
This macro is in the code module for ThisWorkbook:
VBA Code:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If ActiveSheet.Name Like "*financials*" Then
        If Target.CountLarge > 1 Then Exit Sub
        If Intersect(Target, ActiveSheet.Range("O16:O" & ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row)) Is Nothing Then Exit Sub
        CalendarFrm.Show
    End If
End Sub
These macros are in the code module for Sheet "reports":
VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, ActiveSheet.Range("B1,E1,H1")) Is Nothing Then Exit Sub
    Dim ws As Worksheet, val As String
    Application.ScreenUpdating = False
    Select Case Target.Column
        Case Is = 2, 5
            CalendarFrm.Show
        Case Is = 8
            For Each ws In Sheets
                If ws.Name Like "*financials*" Then
                    If val = "" Then val = ws.Name Else val = val & "," & ws.Name
                End If
            Next ws
            With Target.Validation
                .Delete
                .Add Type:=xlValidateList, Formula1:=val
            End With
    End Select
    Application.ScreenUpdating = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, ActiveSheet.Range("H1")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim ldateto As Long, ldatefrom As Long, LastRow As Long, srcWS As Worksheet
    If Target <> "" Then
        Set srcWS = Sheets(Target.Value)
        LastRow = srcWS.Range("A" & Rows.Count).End(xlUp).Row
        ldatefrom = Target.Offset(, -6)
        ldateto = Target.Offset(, -3)
        With ActiveSheet.UsedRange.Offset(3)
            .ClearContents
            .Interior.ColorIndex = xlNone
        End With
        With srcWS
            .Range("A15:O" & LastRow).AutoFilter Field:=15, Criteria1:=">=" & ldatefrom, Operator:=xlAnd, Criteria2:="<=" & ldateto
            .AutoFilter.Range.Offset(1).Copy Range("A4")
            .Range("A15").AutoFilter
        End With
    End If
    Application.ScreenUpdating = True
End Sub
Thanks for that! most appreciated! got it working.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
12,193
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
You are very welcome. :)
 

Forum statistics

Threads
1,176,511
Messages
5,903,470
Members
435,031
Latest member
traceson

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top