Thanks Kassie... ;-)
--
"excel newbie"


"Kassie" wrote:

The list range is your actual data table. Place your cursor on te first line
of the list, and provided that there are no empty rows, it should
automatically pick up the complete table.

To create the criteria range, copy your headings to another location (I
think I said this?). Insert te formulae I recommended below the date
heading, and then block the headings, and down to the last row. Give this a
range name. Iow, click on the address bar, and type in the name you want to
use, eg criteria.This range is used to determine what must be extracted.
Your output range - call it report if you wish, is where the results will
appear. This range should be long enough to cater for the maximum number of
rows you may need to extract.

Then again, Max's formula is a lot simpler. I would abide by his superior
knowledge, if I were you. However, just to learn, this could be a good
exercise!
--
Hth

Kassie Kasselman
Change xxx to hotmail


"melmac" wrote:

hi Kassie,

I was tryin out what you've said, however im having a problem with the
advance filter. Im not sure i understand what you meant in establishing the
criteria range, the advance filter is asking for a 'List Range'. I don't know
where to get that, or is it the first 8 rows of my existing table? And is the
criteria range the range where the results should appear...hehehe please
help...i know i sound stupid, but hey i'm a newbie...hehehehe. I've actually
tried what max said, and it worked out alright, but i want to try what you
gave as well...just really intrested to learn how to use macros and how the
different techniques can have the same result. Please advise...

thanks,
--
"excel newbie"


"Kassie" wrote:

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"

--
Hth

Kassie Kasselman
Change xxx to hotmail


"melmac" wrote:

hi there everyone,

I'm hopeful that someone can help me with my excel problem. Here's my
dilemma: I'm trying to do a results table where results will come from a
worksheet that has different columns, one of which is a date column(let's
call it worksheet A). Now the results table is in another worksheet in the
same workbook, and before results are generated, I'd like the user to specify
a date and then the results table will be populated with entries from
worksheet a that matches the specified date and dates from the previous week.
im not quite sure if it's possible in excel, however if it is possible, what
type of control should i use as the results table. Please help...

thanks in advance,
--
"excel newbie"