Excel - pull data based on criteria

[miked18]

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?

 

[mumps]

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.
--------------------------------------------------------

 

[miked18]

Cub Pack Budget and Finances V3.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	CUB PACK		BANK BALANCE				POPCORN SUBSIDY					DUES			DONATIONS
2	SEPT 2021-SEPT 2022		START	$ 2,391.03			START	$ -				START	$ -		START	$ -
3	NUMBER OF YOUTH		INCOME	$ 1,505.35			INCOME	$ 1,357.19				INCOME	$ 1,200.00		INCOME	$ -
4	24		EXPENSES	$ 1,531.41			EXPENSES	$ -				EXPENSES	$ -		EXPENSES	$ -
5	NUMBER OF SCOUTERS		BALANCE	$ 2,364.97			BALANCE	$ 1,357.19				BALANCE	$ 1,200.00		BALANCE	$ -
6	4
7	PROPOSED BUDGET
8	$ 7,428.00
9	PROPOSED SUBSITY		TOTAL MONEY
10	$ 210.00		AVAILABLE	$ 4,922.16
11	YOUTH FEES TO COLLECT
12	$ 200.00
13
14	ACTIVITY		PROPOSED BUDGET				ACTUAL EXPENSES					ACTUAL INCOME			DATE
15			ESTIMATED COST	SUBSIDY TO USE	YOUTH FEES COLLECTED		COST	EXPENSE TYPE	SUBSIDY USED	ACCOUNT USED FOR SUBSIDY		AMOUNT	INCOME TYPE
16
17
18
19	Popcorn		$ 2,478.00									$ 1,357.19	Popcorn Subsidy		2021-09-01
20	Apple Day
21	Equipment		$ 500.00
22	Dues		$ 1,250.00									$ 1,200.00	Dues		2022-03-01
23	Kub Kars		$ 210.00	$ 210.00	$ 200.00
24	Bank Charges		$ 10.00
25	Badges & Investiture		$ 500.00
26	Uniform Accessories		$ 500.00
27	Lair of the Month		$ 300.00
28	Year End Party
29	Supplies
30	Swimming
31	Bowling
32	Camp - Fall		$ 500.00
33	Camp - Winter		$ 680.00
34	Camp - Spring		$ 500.00
35	Camp - Summer
36	JOTT
37	All Sections Day											$ 95.00	Bank E-Transfer		2021-10-29
38	Bank Interest - Sept 21
39	Bank Interest - Oct 21											$ 0.35	Bank Interest		2021-10-01
40	Bank Interest - Nov 21
41	Bank Interest - Dec 21
42	Bank Interest - Jan 21
43	Bank Interest - Feb 21
44	Bank Interest - Mar 21
45	Bank Interest - Apr 21
46	Bank Interest - May 21
47	Bank Interest - Jun 21
48	Bank Interest - Jul 21
49	Bank Interest - Aug 21
50	E-transfer											$ 600.00	Bank E-Transfer		2022-01-06
51	E-transfer											$ 400.00	Bank E-Transfer		2022-01-06
52	E-transfer											$ 400.00	Bank E-Transfer		2022-01-06
53	Supplies						$ 65.03	Equipment/Supplies							2021-10-05
54	Supplies						$ 257.38	Equipment/Supplies							2021-10-05
55	Supplies						$ -	Equipment/Supplies							2022-02-16
56	Turtle Conserv Presentation						$ 226.00	Activity							2021-09-18
57	Brooks Family Farm						$ 154.00	Activity	$ 90.00	Youth Fees Collected		$ 90.00	Youth Fees Collected		2021-10-19
58	Forsythe Farms						$ 110.00	Activity	$ 85.00	Youth Fees Collected		$ 85.00	Youth Fees Collected		2021-10-27
59	E-transfer											$ 5.00	Bank E-Transfer		2021-12-06
60	E-transfer											$ 5.00	Bank E-Transfer		2021-12-10
61	Treetop Trekking						$ 544.00	Activity							2021-12-04
Cub Pack Financials 2021-2022

Cell Formulas
Range	Formula
D3	D3	=SUMPRODUCT(SUMIF(M19:M68,TBL_BANK_DIRECT[INCOME DIRECT TO BACK ACCOUNT],L19:L68))
D4	D4	=G71+I71
D5,P5,M5,H5	D5	=D2+D3-D4
H3	H3	=SUMIF(M19:M69,G1,L19:L69)
H4	H4	=SUMIF(J19:J68,G1,I19:I68)
M3	M3	=SUMIF(M19:M69,L1,L19:L69)
M4	M4	=SUMIF(J19:J68,L1,I19:I68)
P3	P3	=SUMIF(M19:M69,O1,L19:L69)
P4	P4	=SUMIF(J19:J68,O1,I19:I68)
A8	A8	=C71
A10	A10	=D71
D10	D10	=D5+H5+M5+P5+S5+Y5+V5
A12	A12	=E71

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A59:A60	Expression	="if($A:$B,$A$7=""FR"")"	text	NO
A47:A52	Expression	="if($A:$B,$A$7=""FR"")"	text	NO
A41:A52,A59:A60	Expression	=B:B="sub"	text	NO
A41:A46,AF16:AJ17	Expression	="if($A:$B,$A$7=""FR"")"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
M19:M61	List	=INCOME_TABLE
J19:J61	List	=TBL_SUBSIDY_ACCOUNTS
H19:H55	List	=EXPENSES_TABLE
H56:H61	List	=Tables!$A$5:$A$10

 

[miked18]

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]

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]

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:

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":

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]

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:

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":

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]

You are very welcome.