Question 1:
How would I modify the formula below to include other dates? I
tried the following but get an #VALUE! error:
=IF(TEXT(C8,"MMDD")=OR("1031","0115","0417"),"text ","")
My original formula works, but is only for 1 day:
=IF(TEXT(C8,"MMDD")="1031","text","")

Question 2:
I know that Excel 2000 disables the autofilter function when
the worksheet is protected and hence copied and modified a macro into
"ThisWorkbook" that would fix this. However, I also just read that the
sorting function is also disabled in Excel 2000. So my question is...how
would I modify my existing macro (or add a new macro) to also allow me to
sort? Here is the macro that I have in "ThisWorkbook":

Private Sub Workbook_Open()
'check for filter, turn on if none exists
With Worksheets("Worksheet")
If Not .AutoFilterMode Then
..Range("A1").AutoFilter
End If
..EnableAutoFilter = True
..Protect Password:="temp", _
Contents:=True, UserInterfaceOnly:=True
End With
End Sub

=IF(OR(TEXT(C8,"MMDD")="1031",TEXT(C8,"MMDD")="011 5",TEXT(C8,"MMDD")="0417")
,"text","")


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RS" wrote in message
...
Question 1:
How would I modify the formula below to include other dates? I
tried the following but get an #VALUE! error:
=IF(TEXT(C8,"MMDD")=OR("1031","0115","0417"),"text ","")
My original formula works, but is only for 1 day:
=IF(TEXT(C8,"MMDD")="1031","text","")

Question 2:
I know that Excel 2000 disables the autofilter function when
the worksheet is protected and hence copied and modified a macro into
"ThisWorkbook" that would fix this. However, I also just read that the
sorting function is also disabled in Excel 2000. So my question is...how
would I modify my existing macro (or add a new macro) to also allow me to
sort? Here is the macro that I have in "ThisWorkbook":

Private Sub Workbook_Open()
'check for filter, turn on if none exists
With Worksheets("Worksheet")
If Not .AutoFilterMode Then
.Range("A1").AutoFilter
End If
.EnableAutoFilter = True
.Protect Password:="temp", _
Contents:=True, UserInterfaceOnly:=True
End With
End Sub

Dear Bob,

Thanks for your suggestion. Your equation works without giving an
error. However, because I want different text to appear on different days, I
modified my formula to work as follows:
=IF(TEXT(C8,"MMDD")="1031","text",IF(TEXT(C8,"MMDD ")="0115","text2",IF(TEXT(C8,"MMDD")="0417","text3 ",""))).

Would there be a shorter equation to this? Also, it seems like I
might need to repost the 2nd question regarding macros again (unless you or
someone you know might be able to solve my problem).

"Bob Phillips" wrote:

=IF(OR(TEXT(C8,"MMDD")="1031",TEXT(C8,"MMDD")="011 5",TEXT(C8,"MMDD")="0417")
,"text","")


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RS" wrote in message
...
Question 1:
How would I modify the formula below to include other dates? I
tried the following but get an #VALUE! error:
=IF(TEXT(C8,"MMDD")=OR("1031","0115","0417"),"text ","")
My original formula works, but is only for 1 day:
=IF(TEXT(C8,"MMDD")="1031","text","")

Question 2:
I know that Excel 2000 disables the autofilter function when
the worksheet is protected and hence copied and modified a macro into
"ThisWorkbook" that would fix this. However, I also just read that the
sorting function is also disabled in Excel 2000. So my question is...how
would I modify my existing macro (or add a new macro) to also allow me to
sort? Here is the macro that I have in "ThisWorkbook":

Private Sub Workbook_Open()
'check for filter, turn on if none exists
With Worksheets("Worksheet")
If Not .AutoFilterMode Then
.Range("A1").AutoFilter
End If
.EnableAutoFilter = True
.Protect Password:="temp", _
Contents:=True, UserInterfaceOnly:=True
End With
End Sub

You could try

=VLOOKUP(TEXT(C8,"MMDD"),{"1031","text";"0115","te xt2";"0417","text3"},2,FAL
SE)


On the second question, you either need top sort at the point where you
unprotect the sheet, or add your own sort button which unprotects the sheet,
sorts it, and re-protects it.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RS" wrote in message
...
Dear Bob,

Thanks for your suggestion. Your equation works without giving an
error. However, because I want different text to appear on different
days, I
modified my formula to work as follows:

=IF(TEXT(C8,"MMDD")="1031","text",IF(TEXT(C8,"MMDD ")="0115","text2",IF(TEXT(
C8,"MMDD")="0417","text3",""))).

Would there be a shorter equation to this? Also, it seems like I
might need to repost the 2nd question regarding macros again (unless you
or
someone you know might be able to solve my problem).

"Bob Phillips" wrote:


=IF(OR(TEXT(C8,"MMDD")="1031",TEXT(C8,"MMDD")="011 5",TEXT(C8,"MMDD")="0417")
,"text","")


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RS" wrote in message
...
Question 1:
How would I modify the formula below to include other dates? I
tried the following but get an #VALUE! error:
=IF(TEXT(C8,"MMDD")=OR("1031","0115","0417"),"text ","")
My original formula works, but is only for 1 day:
=IF(TEXT(C8,"MMDD")="1031","text","")

Question 2:
I know that Excel 2000 disables the autofilter function when
the worksheet is protected and hence copied and modified a macro into
"ThisWorkbook" that would fix this. However, I also just read that the
sorting function is also disabled in Excel 2000. So my question
is...how
would I modify my existing macro (or add a new macro) to also allow me
to
sort? Here is the macro that I have in "ThisWorkbook":

Private Sub Workbook_Open()
'check for filter, turn on if none exists
With Worksheets("Worksheet")
If Not .AutoFilterMode Then
.Range("A1").AutoFilter
End If
.EnableAutoFilter = True
.Protect Password:="temp", _
Contents:=True, UserInterfaceOnly:=True
End With
End Sub

Hi there. The formula works on the days provided but on other days, I get a
"#N/A." So...2 questions: 1) what do the last two items (...,2,FALSE) mean?
and 22) how would I change your formula to display a blank on other days?

Regarding the macro, since the number of rows needing to be sorted would be
constantly changing, sounds like it might be easier to find a later version
of Excel and then simply resave my spreadsheets using the new versions
ability to simply select a check box to allow sorting and autofiltering.

"Bob Phillips" wrote:

You could try

=VLOOKUP(TEXT(C8,"MMDD"),{"1031","text";"0115","te xt2";"0417","text3"},2,FAL
SE)


On the second question, you either need top sort at the point where you
unprotect the sheet, or add your own sort button which unprotects the sheet,
sorts it, and re-protects it.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RS" wrote in message
...
Dear Bob,

Thanks for your suggestion. Your equation works without giving an
error. However, because I want different text to appear on different
days, I
modified my formula to work as follows:

=IF(TEXT(C8,"MMDD")="1031","text",IF(TEXT(C8,"MMDD ")="0115","text2",IF(TEXT(
C8,"MMDD")="0417","text3",""))).

Would there be a shorter equation to this? Also, it seems like I
might need to repost the 2nd question regarding macros again (unless you
or
someone you know might be able to solve my problem).

"Bob Phillips" wrote:


=IF(OR(TEXT(C8,"MMDD")="1031",TEXT(C8,"MMDD")="011 5",TEXT(C8,"MMDD")="0417")
,"text","")


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RS" wrote in message
...
Question 1:
How would I modify the formula below to include other dates? I
tried the following but get an #VALUE! error:
=IF(TEXT(C8,"MMDD")=OR("1031","0115","0417"),"text ","")
My original formula works, but is only for 1 day:
=IF(TEXT(C8,"MMDD")="1031","text","")

Question 2:
I know that Excel 2000 disables the autofilter function when
the worksheet is protected and hence copied and modified a macro into
"ThisWorkbook" that would fix this. However, I also just read that the
sorting function is also disabled in Excel 2000. So my question
is...how
would I modify my existing macro (or add a new macro) to also allow me
to
sort? Here is the macro that I have in "ThisWorkbook":

Private Sub Workbook_Open()
'check for filter, turn on if none exists
With Worksheets("Worksheet")
If Not .AutoFilterMode Then
.Range("A1").AutoFilter
End If
.EnableAutoFilter = True
.Protect Password:="temp", _
Contents:=True, UserInterfaceOnly:=True
End With
End Sub

" means get the value from the second column, FALSE means an exact match.

=IF(ISNA(VLOOKUP(TEXT(C8,"MMDD"),{"1031","text";"0 115","text2";"0417","text3
"},2,FALSE)),"",
VLOOKUP(TEXT(C8,"MMDD"),{"1031","text";"0115","tex t2";"0417","text3"},2,FALS
E))




--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RS" wrote in message
...
Hi there. The formula works on the days provided but on other days, I get
a
"#N/A." So...2 questions: 1) what do the last two items (...,2,FALSE)
mean?
and 22) how would I change your formula to display a blank on other days?

Regarding the macro, since the number of rows needing to be sorted would
be
constantly changing, sounds like it might be easier to find a later
version
of Excel and then simply resave my spreadsheets using the new versions
ability to simply select a check box to allow sorting and autofiltering.

"Bob Phillips" wrote:

You could try


=VLOOKUP(TEXT(C8,"MMDD"),{"1031","text";"0115","te xt2";"0417","text3"},2,FAL
SE)


On the second question, you either need top sort at the point where you
unprotect the sheet, or add your own sort button which unprotects the
sheet,
sorts it, and re-protects it.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RS" wrote in message
...
Dear Bob,

Thanks for your suggestion. Your equation works without giving
an
error. However, because I want different text to appear on different
days, I
modified my formula to work as follows:


=IF(TEXT(C8,"MMDD")="1031","text",IF(TEXT(C8,"MMDD ")="0115","text2",IF(TEXT(
C8,"MMDD")="0417","text3",""))).

Would there be a shorter equation to this? Also, it seems like
I
might need to repost the 2nd question regarding macros again (unless
you
or
someone you know might be able to solve my problem).

"Bob Phillips" wrote:



=IF(OR(TEXT(C8,"MMDD")="1031",TEXT(C8,"MMDD")="011 5",TEXT(C8,"MMDD")="0417")
,"text","")


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RS" wrote in message
...
Question 1:
How would I modify the formula below to include other dates? I
tried the following but get an #VALUE! error:
=IF(TEXT(C8,"MMDD")=OR("1031","0115","0417"),"text ","")
My original formula works, but is only for 1 day:
=IF(TEXT(C8,"MMDD")="1031","text","")

Question 2:
I know that Excel 2000 disables the autofilter function when
the worksheet is protected and hence copied and modified a macro
into
"ThisWorkbook" that would fix this. However, I also just read that
the
sorting function is also disabled in Excel 2000. So my question
is...how
would I modify my existing macro (or add a new macro) to also
allow me
to
sort? Here is the macro that I have in "ThisWorkbook":

Private Sub Workbook_Open()
'check for filter, turn on if none exists
With Worksheets("Worksheet")
If Not .AutoFilterMode Then
.Range("A1").AutoFilter
End If
.EnableAutoFilter = True
.Protect Password:="temp", _
Contents:=True, UserInterfaceOnly:=True
End With
End Sub

RS

Even in later versions of Excel there are restrictions on filtering and sorting
that must be overcome.

The Protect Sheet dialog gives you a number of options to choose from, but
autofilter must be enabled before protecting the sheet.

Sort will only work on rows in which all cells in the range of rows are
unprotected.

Most everybody winds up with code to unprotect, sort, then re-protect.


Gord Dibben MS Excel MVP

On Tue, 31 Oct 2006 21:58:02 -0800, RS wrote:

Regarding the macro, since the number of rows needing to be sorted would be
constantly changing, sounds like it might be easier to find a later version
of Excel and then simply resave my spreadsheets using the new versions
ability to simply select a check box to allow sorting and autofiltering.