Database

[Guest]

I have a spreadsheet that includes 500 rows and 15 columns. The data includes
Name, Address, Phone per column and 2 columns of effective dates, one for
each contract. I am trying to create a report that will give me the name of
each individual where each contract expires in a given month. I have formated
the columns to show months only. have tried Filter and Pivot but neither
work. I need an "AND" solution.

ANy help would be appreciated.

Jim

 

[JulieD]

Hi

not sure why filter didn't work for you ... did you try clicking on the date
column drop down arrow, choosing custom and then
greater than or equal to 4/1/05
and
less than or equal to 4/30/05

where you're looking for the current month (change dates as applicable)

 

[Guest]

Another solution would be to add additional columns that refer to the date
using this formula (Assumes Date is in column C

=TEXT(C3,"mmm")

It would be an absolute text string an filter will work just fine. If you
need year also then another syntax might be:

=TEXT(C3,"yy-mmm")

Regards,
Frank T

 

[Guest]

I can get each column to filter but it excludes items in the second column.
Example, column C Column D
06 04
05 06
05 06
04 05

When filtering Column C for 06 the only option I have in Column D would be
04. I need each column to show 06.

Thanks
Jim

 

[JulieD]

Hi Jim

then you could always use advance filter

there are a few different approaches ... the way i do it is
say my headings are currently in row 1 and my data starts in row 2
i insert say 5 rows above this, moving the headings to row 6 and the data to
row 7
then i copy row 6 (the headings) to row 1
then under
C on row 2 type
06
under D on row 3 type
06

now click in row 7 choose data / filter / advanced filter
the list range should be automatically selected for you by excel
click in the criteria range and hightlight the headings in row1 and then row
2 & 3
click OK

check out www.contextures.com/tiptech.html for more details on advanced
filtering