One alternative play which would also deliver the required results
is illustrated in this sample construct:
http://www.savefile.com/files/646903
Reflect selected info in another sht.xls
Source table assumed in sheet: X, cols A to C,
with the key col = col B (Month Awarded) <contains "1st-of-month" real dates
Data assumed running in row2 down
In sheet: Agenda,
Create a DV to select the month-year in C2
(use a defined range: Month created in the index sheet: I)
Select C2, click Data Validation. Allow: List, Source: =Month
Then place
In A4:
=IF(X!B2="","",IF(X!B2=$C$2,ROW(),""))
Leave A1:A3 blank. This is the criteria col.
In B4:
=IF(C4="","",ROW(A1))
Col B is to provide a simple serial numbering corresponding to what's
extracted in col C
In C4:
=IF(ROW(A1)COUNT($A:$A),"",INDEX(X!A:A,SMALL($A:$ A,ROW(A1))-2))
In D4:
=IF(ROW(A1)COUNT($A:$A),"",INDEX(X!C:C,SMALL($A:$ A,ROW(A1))-2))
Cols C & D will extract the name and award amts from X's cols A and C, via
the indexed cols viz: INDEX(X!A:A,... & INDEX(X!C:C,...
Select A4:D4, copy down to cover the max expected extent of data in X's col
B, say down to D200?. Hide away col A. Cols B to D will return the required
results from X, depending on the month-year selected in the DV cell C2.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Aggie G" wrote:
A pivot table will not work for what I want. Let me provide you more
information.
Every month we have a staff meeting where employees are recognized and given
awards. The spreadsheet contains all the information about each award such as
the name of the recipient, the month award, the amount of time off, etc. I
want this information to populate on another sheet (Agenda), but I only want
the information associated with the month of the awared. As an example; if I
have the following:
Name Month Awarded Award Amount
Wylie, Amberlyn January-07 2
Boyett, Joann February-07 1
Brown, Josh February-07 2
Using this information, I want the name and the award amount for the month
of February to populate in the Agenda sheet. How can I do this?