One way is to use an advanced filter with a macro or two, and then a formula?
Set up an advanced filter in Worksheet A, where you will copy the results to
a different location, also in Worksheet A. - Data, Filter, Advanced Filter.
To set up this advanced filter, you will require a criteria range and an
output range, anda macro to control the advanced filtering, as well as to
clean up afterwards.
Say your existing data are found in Worksheet A, Range A1:G150. Now set up
your criteria range. To do this, use the headings of your existing table,
say A1:G1, to the right of the existing table. Say you use columns AA1:AG8
for the criteria range, to allow for criteria for 7 days. Name this
range"Criteria"
In AA2:AA7, insert a formula to use the date below - 1
In AA2, enter =if(AA3="","",AA3-1), and copy down to AA7.
To insert the start date in AA8, you will use a formula, so leave that for
now.
Now copy your headings to AA11:AG11.
Set up an adequate range below this as an output range. Obviously, if you
only have one result per day, then 7 rows would be adequate, else you would
use more. Name this range "Extract".
In your results sheet, set up an output range which will refer to the output
range in Worksheet A.
Again, use your headings as in Worksheet A A1:G1, in A1:G1
In A2 insert a formula to set the value of the cell equal to Worksheet A,
cell AA12
eg. =if('Worksheet A'!AA12="","",'Worksheet A'!AA12).
Copy this across to AG12, and then copy this row down as far as you want to
go.
Finally, set up the input cell, in the results worksheet, where you can
enter the date.
Say you use cell I1 for this purpose. Go back to Worksheet A, and in cell
AA8, insert the formula =if('Results Sheet'!AI1="","",'Results Sheet'!AI1).
Now the macro:
Press <Alt><F11> to go to the VBA window.
Insert a module, and create the following subroutine:
Const wbOne = worksheets("Worksheet A")
Const wbTwo = Worksheets("Results")
Sub Results()
If Range("AI1")="" then exit sub
Application.screenupdating = False
wbOne.Range("A2").activate
Range(ActiveCell, ActiveCell.End(xlDown)).select
Range(Selection, Selection.end(xlToRight)).Select
Selection.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"Criteria"), CopyToRange:=Range("Extract"), Unique:=False
wbTwo.range("AI1").select
Application.screenupdating = True
End Sub
Assign this macro to either a shortcut key, or else create a button, and
change the display text to "Extract".
It really depends what you want to do with this info now. If you want to
print it, you can. The easiest way is to again use a macro, to print out the
results page, and then to delete the date you input, as well as the criteria
in Worksheet A, and the output range in Worksheet A, so that the worksheet is
ready for the next attempt. Something like:
Sub PrintOut()
Application.screenupdating = False
ActiveSheet.printout
Range("AI1").ClearContents
wbOne.Range("AA8").ClearContents
wbOne.Range("Extract").ClearContents
wbTwo.Range("AI1").activate
Application.screenupdating = True
End Sub
and assign this to another button, with text property set to "Print"
