Database

I

insdoc

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
 
J

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)
 
F

frankt

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
 
I

insdoc

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
 
J

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
 
Top