Parameter Query?

A

AcesUp

All,

I am trying to perform the following tasks within Excel.

Task 1

I have three columns, Date, Game, Score.

Is there a way I can request that Excel only display rows that fall
withinn a cetain date range?

Task 2

Once I have the refined data I would like to consolidate it. I.e
Several games might have been played on the same date I would like to
just have one row per date per game with the score totalled. So if two
games of bowling were played on 21/07/05 with score of 163 and 187. The
row would read,

----A------------B-----------C
21/07/05---Bowling-----350

You get the idea! ;)

Thanks in advance.
AU
 
C

Conrad Carlberg

Task 1: Depending on the complexity of the criteria you want to use for
dates, you could use either Data | AutoFilter or Data | Advanced Filter. Be
sure that the columns that contain your dates and scores are headed by
labels such as "DATE" and "SCORE".

Task 2: Create a pivot table (Data | PivotTable and PivotChart Report) with
Date as the Row field and Score as the Data field. If your scores are all
numeric values, the Data field defaults to Sum. However, you might not want
to point the pivot table at your consolidated data if you used AutoFilter to
consolidate it -- all that AutoFilter does is hide rows, but a pivot table
might pick up values in those hidden rows. Safer to use Advanced Filter and
opt to copy the results to another location. Or you can point the pivot
table at your original data and then use the Field Settings in the pivot
table itself to suppress the dates you don't want.

(BTW, the term "parameter query" is normally used to refer to an SQL query
whose results change according to criteria supplied by its users; the
criterion is in that context called a parameter.)

C^2
Conrad Carlberg

Excel Sales Forecasting for Dummies, Wiley, 2005
 
Top