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"

Assume your source table is in sheet: A, cols A to C (say)
data from row2 down, where real dates are in B2 down

In your results sheet,
Assume a specific date will be input in A2

In B2:
=IF($A$2="","",IF(AND(A!B2=$A$2-7,A!B2<=$A$2),ROW(),""))
Leave B1 empty. Col B is the criteria col.

In C2:
=IF(ROWS($1:1)COUNT($B:$B),"",INDEX(A!A:A,SMALL($ B:$B,ROWS($1:1))))
Copy C2 to E2. Select A2:E2, copy down to cover the max expected extent of
data in A. Format col D as dates, minimize/hide away col B. Cols C to E will
return the source lines from A with dates within 7 previous days of the date
specified in A2 (inclusive), as desired.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"melmac" wrote:
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"

Hi Max,

You're a great help! many many many thanks! :-)

--
"excel newbie"


"Max" wrote:

Assume your source table is in sheet: A, cols A to C (say)
data from row2 down, where real dates are in B2 down

In your results sheet,
Assume a specific date will be input in A2

In B2:
=IF($A$2="","",IF(AND(A!B2=$A$2-7,A!B2<=$A$2),ROW(),""))
Leave B1 empty. Col B is the criteria col.

In C2:
=IF(ROWS($1:1)COUNT($B:$B),"",INDEX(A!A:A,SMALL($ B:$B,ROWS($1:1))))
Copy C2 to E2. Select A2:E2, copy down to cover the max expected extent of
data in A. Format col D as dates, minimize/hide away col B. Cols C to E will
return the source lines from A with dates within 7 previous days of the date
specified in A2 (inclusive), as desired.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"melmac" wrote:
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"

Welcome. Do take a moment to press the "Yes" button below
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"melmac" wrote:
Hi Max,

You're a great help! many many many thanks! :-)

--
"excel newbie"

hi max,

Need you help again. I tried your fomula and did what you instructed and it
worked on a sample worksheet. However when i tried applying to my real
worksheet, it doesn't work. I get 0 results even though there are matching
rows from the source table. Alright here's what i have:

the source table is in, let's say worksheet A:
the entries start at row 8 so i have:

A B C D
1
X
7 Name Date Status
8 mel1 4/8/2008 New
9
so on and so forth...
now the results table is in, let's say worksheet B
I changed the formula so it will refer to B8 to check for the date in
worksheet A. I dont get any errors however my results table looks like this

A B C D
E
..
..
..
38 Name Date
Status
39 Date(Input) 39 0 0
0
40 40 0 0
0
41
this is what happens...this is with 2 matching rows on the source table.
Can't figure out whats wrong. Can you also explain the formula to me as well
so i can better undertand how to use it? Is that ok? I know im asking a lot
here...but please i do need help. With the formula you gave, this is my
understanding...
=IF($A$2="","",IF(AND(A!B2=$A$2-7,A!B2<=$A$2),ROW(),""))
-if value of B2 is in between $a$2 and $a$2-7 then get row? cnt seem to
understand what row() does?
=IF(ROWS($1:1)COUNT($B:$B),"",INDEX(A!A:A,SMALL($ B:$B,ROWS($1:1))))
- really cant get this one, dont know how it relates to the result. :-(


help!

thanks,
--
"excel newbie"


"Max" wrote:

Assume your source table is in sheet: A, cols A to C (say)
data from row2 down, where real dates are in B2 down

In your results sheet,
Assume a specific date will be input in A2

In B2:
=IF($A$2="","",IF(AND(A!B2=$A$2-7,A!B2<=$A$2),ROW(),""))
Leave B1 empty. Col B is the criteria col.

In C2:
=IF(ROWS($1:1)COUNT($B:$B),"",INDEX(A!A:A,SMALL($ B:$B,ROWS($1:1))))
Copy C2 to E2. Select A2:E2, copy down to cover the max expected extent of
data in A. Format col D as dates, minimize/hide away col B. Cols C to E will
return the source lines from A with dates within 7 previous days of the date
specified in A2 (inclusive), as desired.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"melmac" wrote:
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"

Use this revised set, since your source data starts in row 8
(source data in sheet: A is assumed between row 8 to 100)

In sheet: B,
Assume a specific date will be input in A2 (as before)
In B2:
=IF($A$2="","",IF(AND(A!B8=$A$2-7,A!B8<=$A$2),ROWS($1:1),""))

In C2:
=IF(ROWS($1:1)COUNT($B$8:$B$100),"",INDEX(A!A$8:A $100,SMALL($B$8:$B$100,ROWS($1:1))))
Copy C2 to E2. Select A2:E2, copy down to cover the max expected extent of
data in A. Format col D as dates, minimize/hide away col B. Cols C to E will
return the source lines from A with dates within 7 previous days of the date
specified in A2 (inclusive), as desired.

Col B is the criteria col which will flag source lines satisfying the
criteria with arbitrary row numbers. These flags will be read by the
index/small formulas placed in cols C to E to "float up" the corresponding
results.

The front IF check, ie:
=IF(ROWS($1:1)COUNT($B$8:$B$100),"", ...
is to produce neat looking blank lines ("") once all the results are
exhausted (instead of #NUM errors). COUNT will return the number of result
lines satisfying the criteria, while ROWS($1:1) is a simple incrementer
returning the numbers: 1,2,3 ... as you copy down. So once the ROWS exceed
the COUNT in the copy down, the IF will evaluate to TRUE, and blank lines
("") will ensue.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"melmac" wrote:
hi max,

Need you help again. I tried your fomula and did what you instructed and it
worked on a sample worksheet. However when i tried applying to my real
worksheet, it doesn't work. I get 0 results even though there are matching
rows from the source table. Alright here's what i have:

the source table is in, let's say worksheet A:
the entries start at row 8 so i have:

A B C D
1
X
7 Name Date Status
8 mel1 4/8/2008 New
9
so on and so forth...
now the results table is in, let's say worksheet B
I changed the formula so it will refer to B8 to check for the date in
worksheet A. I dont get any errors however my results table looks like this

A B C D
E
.
.
.
38 Name Date
Status
39 Date(Input) 39 0 0
0
40 40 0 0
0
41
this is what happens...this is with 2 matching rows on the source table.
Can't figure out whats wrong. Can you also explain the formula to me as well
so i can better undertand how to use it? Is that ok? I know im asking a lot
here...but please i do need help. With the formula you gave, this is my
understanding...
=IF($A$2="","",IF(AND(A!B2=$A$2-7,A!B2<=$A$2),ROW(),""))
-if value of B2 is in between $a$2 and $a$2-7 then get row? cnt seem to
understand what row() does?
=IF(ROWS($1:1)COUNT($B:$B),"",INDEX(A!A:A,SMALL($ B:$B,ROWS($1:1))))
- really cant get this one, dont know how it relates to the result. :-(

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"

Hi Kassie,

Thanks! You're a great help! Will try out doing the macro...i've always
wanted to learn how to macros! Thanks again! ;-)


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

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"

And besides Kassie, i think what you've recommended better suites my
requirements regarding the results table. (you're formula's still cool MAX!
:-) ) I do need sumthing that will clear up after using the table...please
help..thanks,

PS hope you can still help MAX, Thanks to you both!

melmac
--
"excel newbie"


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

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"

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"