Excel 2007 Help

[Dnyan]

Hi,

I am making reports in excel 2007 of claims.
there are two different files and the filter option is coming different like:

<TABLE style="WIDTH: 121pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=160 border=0><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><TBODY><TR style="HEIGHT: 23.25pt; mso-height-source: userset" height=31><TD class=xl71 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 121pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 23.25pt; BACKGROUND-COLOR: white" width=160 colSpan=2 height=31> </TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: black" height=16>F</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: black">Date</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>6-7</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=86>18/01/2007</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>6-7</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=86>10/04/2007</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>7-8</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=86>26/10/2007</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>7-8</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=86>02/01/2008</TD></TR></TBODY></TABLE>

in excel 2007 when i click sort option on Date then There is option coming as
1. AtoZ - Sort Oldest to Newest
2. ZtoA - Sort Newest to Oldest and the main option which i want is
3. Date Filter

on the other hand in another excel file options are coming as
1. AtoZ - Sort A to Z
2. ZtoA - Sort Z to A and the main option which i want is
3. Text Filter

so I want the option of Date filter on the place of Text filter.
Please guide me that how I can change this option in other file.

Regards
Dnyan

 

[jasonb75]

At a guess I would say that the dates are an invalid format that excel is not recognising.

 

[Dnyan]

Hi,

Dates are not invalid format.
on date option I select filter option. after that When I click on filter then above mentioned points are coming in two different worksheet.

so I want the option of Date filter on the place of Text filter.
Please guide me that how I can change this option in other file.

 

[jasonb75]

Which column are your dates in on the sheet where it's not working?

Assuming they are in column A, change column to match your data. what is the result of the test formula?

=COUNT(A:A)/COUNTA(A:A)

 

[Dnyan]

Hi,

Hi,

Dates mentioned on B column

<TABLE style="WIDTH: 121pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=160 border=0><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><TBODY><TR style="HEIGHT: 23.25pt; mso-height-source: userset" height=31><TD class=xl71 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 56pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 23.25pt; BACKGROUND-COLOR: white" width=74 height=31> A</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 65pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white" width=86> B</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: black" height=16>F</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: black">Date</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>6-7</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=86>18/01/2007</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>6-7</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=86>10/04/2007</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>7-8</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=86>26/10/2007</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>7-8</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=86>02/01/2008</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>8-9</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=86>23/06/2008</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>9-10</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=86>04/11/2009</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>9-10</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=86>22/01/2010</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>10-11</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=86>15/05/2010</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>10-11</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=86>04/06/2010</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>10-11</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=86>22/07/2010</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>10-11</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=86>25/10/2010</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>10-11</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=86>09/11/2010</TD></TR></TBODY></TABLE>

I dont want any formula for compare the column
the query is I have selected filter on date option.
and on that filter when I click then only
There is option coming as
1. AtoZ - Sort Oldest to Newest
2. ZtoA - Sort Newest to Oldest and the main option which i want is
3. Date Filter

on the other hand in another excel file options are coming as
1. AtoZ - Sort A to Z
2. ZtoA - Sort Z to A and the main option which i want is
3. Text Filter

 

[jasonb75]

I didn't mention the formula was an intended solution, it's a test to try and establish the cause of the problem.

What is the result of

=COUNT(B:B)/COUNTA(B:B)

It should return a decimal value between 0 and 1, after you reply with that value you can delete the formula.

 

[Dnyan]

Hi,

after putting =COUNT(B:B)/COUNTA(B:B) this the answer is coming "0" in both the worksheet.

 

[jasonb75]

Are your dates produced by formula?

 

[Dnyan]

Hi,

No, Dates are typed by manually.

 

[jasonb75]

There must be something other than dates in column B, either that or they are not valid dates.

What do you get from testing with

=COUNT(B:B)

and

=COUNTA(B:B)

Results from both sheets please.