results table dilemma

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"
0
MelMac (10)
5/3/2008 6:45:01 PM
excel.newusers 15348 articles. 2 followers. Follow

11 Replies
517 Views

Similar Articles

[PageSpeed] 33

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"
0
demechanik (4694)
5/4/2008 11:44:00 AM
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"
0
5/4/2008 1:33:00 PM
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"
0
MelMac (10)
5/10/2008 12:43:00 AM
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"
0
MelMac (10)
5/10/2008 12:45:22 AM
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"
0
MelMac (10)
5/10/2008 4:53:00 AM
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"
0
MelMac (10)
5/10/2008 5:22:00 AM
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"
0
5/10/2008 6:09:01 AM
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"
0
MelMac (10)
5/11/2008 2:08:00 AM
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"

0
demechanik (4694)
5/12/2008 6:17:00 AM
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"
0
MelMac (10)
5/15/2008 1:16:03 PM
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. :-( 
0
demechanik (4694)
5/15/2008 3:21:05 PM
Reply:

Similar Artilces:

Please help me decipher the result of COUNT.
Following data from A1:A7 Sales 12/8/2008 19 22.24 TRUE #DIV/0! =COUNT(A1:A7,2) returns 4. I don't understand where the four occurrences of 2 come from. 22.24 = 3 occurrences 12/8/2008 = 2 occurrences total of 5?? I know I am missing something. Please tell me. Is there a tool that I can use to trace? I tried the formula auditing toolbar, no luck because there is no error and trace precedents is not detailed enough. Appreciate explanation. Epinn have a look in the help index for COUNT -- Don Guillett SalesAid Software dguillett1@austin.rr...

Pivot Table date format
The "data" worksheet has the correct date format -mm/dd/yy. There is no time stamp When in the pivot table worksheet the date is showing up as :00, :01, :02...... What appears to be a truncated time format. I have tried to set the date format in both the data worksheet and the pivot table worksheet with no changes evident. The data worksheet will change but this is not carried over to the pivot table worksheet. Setting the format in the pivot table worksheet has no effect. Any suggestions?? Hi Irene, I have just tried to reproduce the error that you are seein...

If statement result appearing in different colors
Is there a way to write an IF statement where the text will change color depending on the result? eg =if(x=0,"RED-colored text","BLACK-colored text") where 'RED-colored text' would appear in RED, etc Thank You! WayneD Take a look at Format|Conditional formatting. WGD wrote: > > Is there a way to write an IF statement where the text will change color > depending on the result? eg > > =if(x=0,"RED-colored text","BLACK-colored text") where 'RED-colored text' > would appear in RED, etc > > Thank ...

Conditional Formatting using result of Cell Formulas
Hi, I'm trying to make a conditional format when the result of a formula results in a value being entered in a cell. I'm using Excel Xp and my formula is as follows. =IF(F160="Need Info",TODAY()-S160,"") I've tried using formulas like >0, <>"", <>BLANK etc. but they all seem to fail. All cells are formatted, even entirely blank cells, as if the condition has been met. BTW, is this formula better strategically than the above formula. =IF(F160<>"Need Info","",TODAY()-S160) Just curious. TIA!!! Norm If ...

Count results from query with no results
I have continous form with an unbound text box [keyword] and a button [search]. There is also an unbound textbox [numberofresults] that counts the number of records returned. If the search produces no results, the [numberofresults] needs to read 0, instead it is just blank. What is the proper way to show zero results as 0 and not blank? It depends on how you are doing your search? Do you have a function? Are you just filtering the recordset? Dale -- email address is invalid Please reply to newsgroup only. "Ryan Tisserand" wrote: > I have continous form with an un...

Pivot table sorting #2
Hello, I have a pivot table report for partnumber and values. This report is based on a database containing details like partnumber source import flag Description total value... etc. In my report i would like to sort the table in descending order of total value. I have tried advanced option by double clicking the total value field and changing the autosorting option to descending(total). But i am not getting the required result. My version of excel is Microsoft office excel 2003. Pivot table report is given below Source (All) Imp (All) Sub (All) Description (All) ...

sql result different to excel result
Hi, Just wondering why I am having such problems rounding figures, when I round the same figure on an Excel Sheet, I end up with a different end result to what appears in sql. The Excel Looks Like This. Net Vat Total Allocated Result (=(NET+VAT)/Allocated*Total) 900.00 157.50 1721.38 1721.38 1057.50 150.00 26.25 1721.38 1721.38 176.25 The Result field is a calculated field and is rounded to 2 decimal places, the calculation is in brackets next to the title name. The Table in SQL has the same structure but the net, vat,...

table in query has no data
Good afternoon- I am trying to create a report that is based upon several tables. In the main table, tblMain, I have the following fields: MainID ProjectName ClientID ---------------------------------> Linked to tblClient ProposalID --------------------------> Linked to tblProposal In a form, a user enters in the new record for tblMain, which requires ProjectName and ClientID to be filled. ProposalID is an optional field. When I create the query, because some records will have a 0 for the cell value of ProposalID, there is no value in the tblProposal and will skip that record ...

Formula to return numerical, text result?
I need to create a formula that based upon three possible factors, wil return one of two calculations or a text message. If cell B="Good", the formula in cell C returns "Text" If cell B="Better", the formula in cell C returns cell A*.3 If cell B="Best", the formula in cell C returns cell A*.5 Thanks in advance for any tips -- burgeo ----------------------------------------------------------------------- burgeon's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2023 View this thread: http://www.excelforum.com/showthread.php...

Sequencing Weekdays in Pivot tables
I have a set of employee data that records employee timecode & hours fo each day. I can create a pivot table to view absenteesim data (identifiable by a 'hours code'. The hours are accumulated into weekday buckets as follows.. Pivot table layout Hrs Code -- Mon Tue Wed Thu Fri Sat Sun SIC 8 DAY 8 DAY 8 SIC 8 ...

Table to Form automatically
I am manually changing inventory on a table and when I go below a certain level I want it to automatically switch to the re order form. Thanks If you mean that you have the table open and are changing values in the table, there is no way to make the table open the re order form. Build a query using that same table, use that query as the record source for a form. Now you can try writing some code to open the re order form when the re order level is reached. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Rainman1960" <Rainman1960@discus...

How to replace a function with its result or resulting reference in a formula?
Hello How to replace a function with its result in a formula? For example, =INDEX(...)+INDEX(...) with =result_of_INDEX(a...)+result_of_INDEX(b...)=1025+1307. How to replace a function with its resulting reference in a formula? For example, =HLOOKUP(1...)+HLOOKUP(2...) with =b11+z11. I need to replace in 1000 cells automatically. "Dmitry Kopnichev" <kopn@bk.ruDelete> �������/�������� � �������� ���������: news:%23cF5PL9zFHA.916@TK2MSFTNGP10.phx.gbl... > Hello > How to replace a function with its result in a formula? For example, > =INDEX(...)+INDEX(...) with > =r...

Pivot Table not populating cells
I have a set of data which I need to put into a PT: Level 1 is Surname and level 2 is First Name. I've sorted it within the PT by Surname then First name. I have 15 instances of the name Jones. When the PT is produced it only shows one instance of th word Jones and then the rest of the lines below (ie the other 14) have no data. The next column to the right has all 15 First names but the only one with Jones next to it, is the first one. How can I get my PT to show ALL surnames next to all the first names please? Thanks in advance, AW The row headings show once in a Pivot Tab...

Records missing after relinking table
A couple of years ago when I was (more) foolish, someone convinced me to move a couple of years' worth of old records from one of my tables into a new, "archive" database, while keeping the more current records in our regular db. I did the move at the time, but then I decided the other day to move them back into their original table in the backend. The move went fine, and all the records were there. But when I went into the front end to relink the table, after relinking I lost about the last 19 days worth of records - several thousand of them. The records were r...

Change Required Field Property in Table
I am trying to change the required field property in for a field in an existing table based on a value in a field in my form. I am using the BeforeUpdate event. Here is what I have so far: tables!tblPayments!tblPaymentsDateWritten.Required = False When the conditions are met, I get a message that says, "Runtime error '424' Object Required." Can the Required property of the field be altered from a form? If so, How can I do it? "Jonas" <sundet@yahoo.com> wrote in message news:68bdf43b-85b9-4ad0-8a38-faee98d6dabf@d2g2000yqa.googlegroups.com......

Hide results
I am putting together a summary spreadsheet of dates from seperate worksheets in an excel file. In the summary sheet I reference the 'end' date from the summary sheet. If there is no date in the original file, what formula can I use to leave that cell blank/hide on the summary sheet? The way I have it now the results show the default of 1/0/1900 Here is the formula used to reference the info from. =Summary!K5 =IF(Summary!K5="","",Summary!K5) -- David Biddulph "bigred72" <bigred72@discussions.microsoft.com> wrote in message ...

Link to CRM tables from external application
My client has a mission-critical application that was written in Microsoft Access sitting on a SQL Server database. It is basically a Quotation management system. The client wishes to install CRM and I'm investigating how to migrate the Customer data from their Quotation system to CRM. I'm pretty confident I can do this without too much trouble (having identified the relevant tables in CRM - AccountBase and ContactBase). However, I then want to link directly to the CRM tables from within the Access application. Normally I'd do this by setting up a DSN and using it to pull in l...

Rounding actual results
Hi, I've got a grid that says: Hr Min Maths: 5 25 English: 7 25 And so on. I'm wanting to work out how much time is devoted to learning in the whole week. What I've got so far is: Hr Min Total learning time: =A1+A2... (counts up all totals in hours column) My current answer for hours is 18. I'm stuck with what to do with the minutes column. so far, I've got an ...

Query to get everyone not in table
Here's what I want to do. I have table 1 with staff member's firstname, lastname, and id#. I have table 2 with staff member's entry#, entrydate, and id#. Relationship between table 1 and table 2 through id# w/ referrential integrity. I want to put a query together that I would input a date and retrieve a list of staff member's who do not have an entry on that date. Basically, I want to find out who didn't show up to the meeting on that date. Does anyone have a solution for this? Robin rbuick wrote: >I have table 1 with staff member's firstname, lastname, and...

2007 Table Linked Charts
I have a dataset with monthly data over several years. I want to chart only the current calendar year's monthly data, and have the chart update automatically as each of the newest month's data is added. One solution is to hide the prior years' monthly data rows in the table which removes them from the chart. This limits other charts that might be linked to the same table where they chart the whole or an earlier date range of the dataset. Linking tables doesn't work since the mirroring tables don't auto update as a new month is entered. Has anyone developed a better sol...

Rotate XLS table
Does anyone know how to move a normal XLS table and make it read right to left Example - instead of a b c, read Thanks, Right to left or top to bottom? Look at Edit | Copy | Edit | Paste Special | Transpose. -- Vasant "Fred" <anonymous@discussions.microsoft.com> wrote in message news:985FACCD-03ED-484D-95E4-04B4C26876EC@microsoft.com... > Does anyone know how to move a normal XLS table and make it read right to left? > > Example - instead of a b c, read > a > b > c > > Thanks, ...

Adding record number to existing table
I'm trying to write an update query to add a record number to a group of records. The table looks like this: ID Name Date Day 1 Jane 1/1/07 1 Jane 1/2/07 2 John 1/5/07 I'm trying to update the "Day" field where the count starts over for each new ID number at 1 and then runs sequentially. Can anyone help? Substitute your table and field names -- SELECT Q.Group, Q.Points, (SELECT COUNT(*) FROM Product Q1 WHERE Q1.[Group] = Q.[Group] AND Q1.Points < Q.Points)+1 AS Rank FROM Product AS Q ORDER BY Q.Group, Q.Points; If you h...

Business Portal 3.0
Hi, I cannot see any Project related data in the Business Portal Queries. I have checked, double-checked and triple-checked security, and all seems valid. My user is a Project Manager and Project Approver. The Project Manager role definitely has Data Permissions access to all the Project objects. There are about 142 projects in total. If I click in Project-Queries-Projects, I get the message: Query Returned No Results Any ideas? Regards Konrad ...

Add the same field twice to a pivot table but filter one of them?
In my datasheet, I have a "cost" column and a "date" column so each cost has an associated date. In my pivot table, I've added the "cost" as a field, which shows the total and this is fine. However, I'd like to add the "cost" as a field again and this time selecting which dates to include in the cost number so that I have two cost fields side by side. Is something like this possible? Hi That is not possible in the same PT. You will need to set up a second PT based upon the same data set as the first but do NOT use the same Pivot Cache to save ...

Pivot table overwrites neighbouring cells #2
Hello, I have multiple pivot tables on the same worksheet to allow for easy comparison of data. The source data is based on the week and expected to be refreshed daily. As the week progresses the size of the pivot tables will grow and overwrite data below it. I am looking for a workaround to this problem or if there is a way to set the pivot table to insert a new row below it if the size will increase and before data is updated. Thanks in advance, Anissa Hi, There is no feature of pivot tables to do what you wish. You pretty much have two options that I can think of: 1. Prepare e...