Data Entry, Lookup and List

[iRule]

I was asked by a local church to come up with a simple excel sheet that would calculate funds raised by about 4-5 products sold at the church. Since I seem to get things done and they do not have anyone who is able to do it, they handed over the problem to me.

The Story:
The priest needs to enter the following information:
Date, Product A, Product B, Product C, Product D, Product E.
He needs the total funds raised by all the products on the input date. So...
01/01/2011 | $50 | $10 | $20 | $10 | $10 = $100
Now I can do that. I was also able to calculate the total for P(A,B,C,D,E) individually when more entries from different dates are entered. Also calculating the Grand Total up to the last entry.

I also had simplified the method of data entry, by using this tutorial:

Save a Form in Excel: Podcast #1272

The Problem:
For reasons unknown to me the priest needs an easy way (because of his limitations with computers) of puling up a certain date range (say all entries between the 01/01/2011 and 30/01/2011) on a separate spreadsheet where the totals for each date entry, for all the products in the period and the grand total would be displayed.

Now, I know the sums would probably be with SUM, but how do I get excel to choose the date range (possibly via a dropdown menu : from - to) and which would then make excel transfer all the required information (meeting the criteria) on a new spreadsheet.
All dates will be chronological.

My Questions:
A. Can it be done in Excel?
B. How hard is it to achieve this for someone like me, with limited excel knowledge?
C. Can anyone on this forum direct me on what functions I will need to use and what tutorials will I need to read (watch) in order to complete the above problem.

Thank You in advance.

p.s. Don't hesitate to ask questions on the problem if something is not clear.

 

[DauntingGecko]

You can use auto filters. Plenty of help online on how to use them and pretty much straight forward to use - even with dates.

 

[HRIS]

You might try the below VBA code or a variation of such. I highly recommend saving a copy of the workbook and/or trying it on a copy of the workbook first! When the macro is run, it will give you two input boxes to choose beginning and end dates. If it works for you, you might attach it to a custom button or a keystroke combination.

I'm pretty new to VBA, so I'm sure there are others out there who can significantly improve upon this!

Sub LocalChurch()
 
'Variables used by the macro
Dim FilterCriteria
Dim CurrentFileName As String
Dim NewFileName As String
'Get the current file's name
CurrentFileName = ActiveWorkbook.Name
'Selects my range
'(note you can change this to meet your requirements)
Range("A1:G65000").Select
'Apply Autofilter
Selection.AutoFilter
'Get the filter's criteria from the user
FilterCriteria1 = InputBox("Begin Date")
FilterCriteria2 = InputBox("End Date")
'Filter the data based on the user's input
'NOTE - this filter is on column A (field:=1), to change to a different column you need to change the field number
Selection.AutoFilter Field:=1, Criteria1:=">=" & FilterCriteria1, Criteria2:="<=" & FilterCriteria2
'Select the visible cells (the filtered data)
Selection.Copy
'Inserts a new sheet, pastes the copied data, formats it
Sheets.Add
Range("A1").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    Cells.Select
    Cells.EntireColumn.AutoFit
'Inputs a formula into cell I1 - location and formula can be changed as necessary
Range("I1") = "Grand Total"
Range("I2").Select
    ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:R[196]C[-2])"
    Sheets("Sheet1").Select
    Selection.AutoFilter Field:=1
    Range("A1").Select
End Sub

Let me know how it goes!