| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
2 different ?'s: Date? Macro?
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
2 different ?'s: Date? Macro?
=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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
2 different ?'s: Date? Macro?
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
2 different ?'s: Date? Macro?
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
2 different ?'s: Date? Macro?
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
2 different ?'s: Date? Macro?
" 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
2 different ?'s: Date? Macro?
That " should of course be 2
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Bob Phillips" wrote in message ... " 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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
2 different ?'s: Date? Macro?
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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
2 different ?'s: Date? Macro?
Dear Bob,
The modification you made to your formula now works. I guess adding the criteria of having different text on different days made the formula a little more complicated and longer. It seems that in this case, my modified formula might be the simpler of the two to use: =IF(TEXT(C8,"MMDD")="1031","text",IF(TEXT(C8,"MMDD ")="0214","text2",IF(TEXT(C8,"MMDD")="0317","text3 ",""))). Thank you very much for all your help! "Bob Phillips" wrote: That " should of course be 2 -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Bob Phillips" wrote in message ... " 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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
2 different ?'s: Date? Macro?
Dear Gord,
Thanks so much for the additional "sort" info, I did not know about those limitations. I already have Autofilter enabled, but I guess in my case, since I have hidden formulas within the rows that would be sorted, that simply checking the allow sort option in later versions wouldn't work. When you said "Most everybody winds up with code to unprotect, sort, then re-protect" would this macro account for increasing numbers of rows as additional info is added to the spreadsheet? If so, what is the code that I would use? "Gord Dibben" wrote: 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. |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
2 different ?'s: Date? Macro?
You can calculate the range like so
Set rng = Range(Range("A1"),Range("A1").End(xlDown)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "RS" wrote in message ... Dear Gord, Thanks so much for the additional "sort" info, I did not know about those limitations. I already have Autofilter enabled, but I guess in my case, since I have hidden formulas within the rows that would be sorted, that simply checking the allow sort option in later versions wouldn't work. When you said "Most everybody winds up with code to unprotect, sort, then re-protect" would this macro account for increasing numbers of rows as additional info is added to the spreadsheet? If so, what is the code that I would use? "Gord Dibben" wrote: 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. |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
2 different ?'s: Date? Macro?
Since I don't know vb coding, how would I incorporate this into the existing
macro? Would I need to paste a new macro code into the spreadsheet? If so, what would the code be? I guess that I would replace A1 with A49 since that's where the sorted data begins. I'm assuming based on Gord's comments, that if the macro has to unprotect, sort, and reprotect, that the person clicking on the macro won't be able to see what the password is? Am I right in this assumption? "Bob Phillips" wrote: You can calculate the range like so Set rng = Range(Range("A1"),Range("A1").End(xlDown)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "RS" wrote in message ... Dear Gord, Thanks so much for the additional "sort" info, I did not know about those limitations. I already have Autofilter enabled, but I guess in my case, since I have hidden formulas within the rows that would be sorted, that simply checking the allow sort option in later versions wouldn't work. When you said "Most everybody winds up with code to unprotect, sort, then re-protect" would this macro account for increasing numbers of rows as additional info is added to the spreadsheet? If so, what is the code that I would use? "Gord Dibben" wrote: 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Mileage Claim Formula | New Users to Excel | |||
| Closing File Error | Excel Discussion (Misc queries) | |||
| NETWORKDAYS - Multiple Date Selection | Excel Discussion (Misc queries) | |||
| RECORDED MACRO PASTE'S DATE DIFFERENTLY TO MANUAL PASTE | Excel Worksheet Functions | |||
| How do I set a date range for conditional formatting in a macro? | Excel Worksheet Functions | |||
Linear Mode
