| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check month
Hi all,
How to set the entry in a specific column must in date type and checking for this date must in a specific month? e.g. in the sheet name "Feb", all the date in column in "A" other than February will not be accepted and prompt a dialog box to ask the user to use another sheet.. Thanks |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check month
You could create data validation for the column, let's say column E for this
example, with a custom type and a formula of =MONTH(E1)=MONTH(DATEVALUE("01-"&MID(CELL("Filename",A1),FIND("]",CELL("filename",A1))+1,99)&"-"&YEAR(TODAY()))) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "jimmy" wrote in message ... Hi all, How to set the entry in a specific column must in date type and checking for this date must in a specific month? e.g. in the sheet name "Feb", all the date in column in "A" other than February will not be accepted and prompt a dialog box to ask the user to use another sheet.. Thanks |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check month
Sorry, what is "filename" for?
I tried to copy & paste your formula to E1 but doesn't, how to test? Thanks.. "Bob Phillips" .gbl... You could create data validation for the column, let's say column E for this example, with a custom type and a formula of =MONTH(E1)=MONTH(DATEVALUE("01-"&MID(CELL("Filename",A1),FIND("]",CELL("filename",A1))+1,99)&"-"&YEAR(TODAY()))) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "jimmy" wrote in message ... Hi all, How to set the entry in a specific column must in date type and checking for this date must in a specific month? e.g. in the sheet name "Feb", all the date in column in "A" other than February will not be accepted and prompt a dialog box to ask the user to use another sheet.. Thanks |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check month
Filename is an argument to the Cell worksheet function telling it to return
the fully qualified path and filename of the workbook (and the sheet name). However, it returns nothing unless the workbook has been saved once (so it has a fully qualified path and filename). -- Regards, Tom Ogilvy "jimmy" wrote in message ... Sorry, what is "filename" for? I tried to copy & paste your formula to E1 but doesn't, how to test? Thanks.. "Bob Phillips" .gbl... You could create data validation for the column, let's say column E for this example, with a custom type and a formula of =MONTH(E1)=MONTH(DATEVALUE("01-"&MID(CELL("Filename",A1),FIND("]",CELL("filename",A1))+1,99)&"-"&YEAR(TODAY()))) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "jimmy" wrote in message ... Hi all, How to set the entry in a specific column must in date type and checking for this date must in a specific month? e.g. in the sheet name "Feb", all the date in column in "A" other than February will not be accepted and prompt a dialog box to ask the user to use another sheet.. Thanks |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check month
My excel file is in the d:Book1.xls, I type the following in the sheet3 cell
"E1", then E1 display 1900/1/0. How to use it to check for the cell in "A1" is my specific valid month? =MONTH(E1)=MONTH(DATEVALUE("01-"&MID(CELL("d:Book1.xls.Sheet3",A1),FIND("]",CELL("d:Book1.xls.Sheet3",A1))+1,99)&"-"&YEAR(TODAY()))) "Tom Ogilvy" bl... Filename is an argument to the Cell worksheet function telling it to return the fully qualified path and filename of the workbook (and the sheet name). However, it returns nothing unless the workbook has been saved once (so it has a fully qualified path and filename). -- Regards, Tom Ogilvy "jimmy" wrote in message ... Sorry, what is "filename" for? I tried to copy & paste your formula to E1 but doesn't, how to test? Thanks.. "Bob Phillips" .gbl... You could create data validation for the column, let's say column E for this example, with a custom type and a formula of =MONTH(E1)=MONTH(DATEVALUE("01-"&MID(CELL("Filename",A1),FIND("]",CELL("filename",A1))+1,99)&"-"&YEAR(TODAY()))) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "jimmy" wrote in message ... Hi all, How to set the entry in a specific column must in date type and checking for this date must in a specific month? e.g. in the sheet name "Feb", all the date in column in "A" other than February will not be accepted and prompt a dialog box to ask the user to use another sheet.. Thanks |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check month
First, the sheet must have a Month name, so change Sheet3 to Mar
Put this in cell E1 of the sheet named Mar (don't change the formula - don't replace "filename") =MONTH(DATEVALUE("01-"&MID(CELL("Filename",A1),FIND("]",CELL("filename",A1))+1,99)&"-"&YEAR(TODAY()))) Now select Column A of the sheet named Mar go to Data=Validation Select Custom and in the textbox put in the formula =Month(A1)=$E$1 and click OK. -- Regards, Tom Ogilvy "jimmy" wrote in message ... My excel file is in the d:Book1.xls, I type the following in the sheet3 cell "E1", then E1 display 1900/1/0. How to use it to check for the cell in "A1" is my specific valid month? =MONTH(E1)=MONTH(DATEVALUE("01-"&MID(CELL("d:Book1.xls.Sheet3",A1),FIND("]",CELL("d:Book1.xls.Sheet3",A1))+1,99)&"-"&YEAR(TODAY()))) "Tom Ogilvy" bl... Filename is an argument to the Cell worksheet function telling it to return the fully qualified path and filename of the workbook (and the sheet name). However, it returns nothing unless the workbook has been saved once (so it has a fully qualified path and filename). -- Regards, Tom Ogilvy "jimmy" wrote in message ... Sorry, what is "filename" for? I tried to copy & paste your formula to E1 but doesn't, how to test? Thanks.. "Bob Phillips" .gbl... You could create data validation for the column, let's say column E for this example, with a custom type and a formula of =MONTH(E1)=MONTH(DATEVALUE("01-"&MID(CELL("Filename",A1),FIND("]",CELL("filename",A1))+1,99)&"-"&YEAR(TODAY()))) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "jimmy" wrote in message ... Hi all, How to set the entry in a specific column must in date type and checking for this date must in a specific month? e.g. in the sheet name "Feb", all the date in column in "A" other than February will not be accepted and prompt a dialog box to ask the user to use another sheet.. Thanks |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check month
Thank you..
In fact, what's the different between using this and just set 01-03-2007 to 31-03-2007 in the Data = Validation? "Tom Ogilvy" bl... First, the sheet must have a Month name, so change Sheet3 to Mar Put this in cell E1 of the sheet named Mar (don't change the formula - don't replace "filename") =MONTH(DATEVALUE("01-"&MID(CELL("Filename",A1),FIND("]",CELL("filename",A1))+1,99)&"-"&YEAR(TODAY()))) Now select Column A of the sheet named Mar go to Data=Validation Select Custom and in the textbox put in the formula =Month(A1)=$E$1 and click OK. -- Regards, Tom Ogilvy "jimmy" wrote in message ... My excel file is in the d:Book1.xls, I type the following in the sheet3 cell "E1", then E1 display 1900/1/0. How to use it to check for the cell in "A1" is my specific valid month? =MONTH(E1)=MONTH(DATEVALUE("01-"&MID(CELL("d:Book1.xls.Sheet3",A1),FIND("]",CELL("d:Book1.xls.Sheet3",A1))+1,99)&"-"&YEAR(TODAY()))) "Tom Ogilvy" bl... Filename is an argument to the Cell worksheet function telling it to return the fully qualified path and filename of the workbook (and the sheet name). However, it returns nothing unless the workbook has been saved once (so it has a fully qualified path and filename). -- Regards, Tom Ogilvy "jimmy" wrote in message ... Sorry, what is "filename" for? I tried to copy & paste your formula to E1 but doesn't, how to test? Thanks.. "Bob Phillips" .gbl... You could create data validation for the column, let's say column E for this example, with a custom type and a formula of =MONTH(E1)=MONTH(DATEVALUE("01-"&MID(CELL("Filename",A1),FIND("]",CELL("filename",A1))+1,99)&"-"&YEAR(TODAY()))) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "jimmy" wrote in message ... Hi all, How to set the entry in a specific column must in date type and checking for this date must in a specific month? e.g. in the sheet name "Feb", all the date in column in "A" other than February will not be accepted and prompt a dialog box to ask the user to use another sheet.. Thanks |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check month
This will vary the validation based upon the sheet name, so you can apply
the same formula to all sheets, not a different one to each. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "jimmy" wrote in message ... Thank you.. In fact, what's the different between using this and just set 01-03-2007 to 31-03-2007 in the Data = Validation? "Tom Ogilvy" bl... First, the sheet must have a Month name, so change Sheet3 to Mar Put this in cell E1 of the sheet named Mar (don't change the formula - don't replace "filename") =MONTH(DATEVALUE("01-"&MID(CELL("Filename",A1),FIND("]",CELL("filename",A1))+1,99)&"-"&YEAR(TODAY()))) Now select Column A of the sheet named Mar go to Data=Validation Select Custom and in the textbox put in the formula =Month(A1)=$E$1 and click OK. -- Regards, Tom Ogilvy "jimmy" wrote in message ... My excel file is in the d:Book1.xls, I type the following in the sheet3 cell "E1", then E1 display 1900/1/0. How to use it to check for the cell in "A1" is my specific valid month? =MONTH(E1)=MONTH(DATEVALUE("01-"&MID(CELL("d:Book1.xls.Sheet3",A1),FIND("]",CELL("d:Book1.xls.Sheet3",A1))+1,99)&"-"&YEAR(TODAY()))) "Tom Ogilvy" bl... Filename is an argument to the Cell worksheet function telling it to return the fully qualified path and filename of the workbook (and the sheet name). However, it returns nothing unless the workbook has been saved once (so it has a fully qualified path and filename). -- Regards, Tom Ogilvy "jimmy" wrote in message ... Sorry, what is "filename" for? I tried to copy & paste your formula to E1 but doesn't, how to test? Thanks.. "Bob Phillips" .gbl... You could create data validation for the column, let's say column E for this example, with a custom type and a formula of =MONTH(E1)=MONTH(DATEVALUE("01-"&MID(CELL("Filename",A1),FIND("]",CELL("filename",A1))+1,99)&"-"&YEAR(TODAY()))) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "jimmy" wrote in message ... Hi all, How to set the entry in a specific column must in date type and checking for this date must in a specific month? e.g. in the sheet name "Feb", all the date in column in "A" other than February will not be accepted and prompt a dialog box to ask the user to use another sheet.. Thanks |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check month
ic..thank you.
The 12 sheets have named as January to December, I wish to write the code in the Workbook open() function that the current month sheet activate...I tried MONTH(TODAY()).ACTIVATE but failed. What the code should be? "Bob Phillips" bl... This will vary the validation based upon the sheet name, so you can apply the same formula to all sheets, not a different one to each. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "jimmy" wrote in message ... Thank you.. In fact, what's the different between using this and just set 01-03-2007 to 31-03-2007 in the Data = Validation? "Tom Ogilvy" bl... First, the sheet must have a Month name, so change Sheet3 to Mar Put this in cell E1 of the sheet named Mar (don't change the formula - don't replace "filename") =MONTH(DATEVALUE("01-"&MID(CELL("Filename",A1),FIND("]",CELL("filename",A1))+1,99)&"-"&YEAR(TODAY()))) Now select Column A of the sheet named Mar go to Data=Validation Select Custom and in the textbox put in the formula =Month(A1)=$E$1 and click OK. -- Regards, Tom Ogilvy "jimmy" wrote in message ... My excel file is in the d:Book1.xls, I type the following in the sheet3 cell "E1", then E1 display 1900/1/0. How to use it to check for the cell in "A1" is my specific valid month? =MONTH(E1)=MONTH(DATEVALUE("01-"&MID(CELL("d:Book1.xls.Sheet3",A1),FIND("]",CELL("d:Book1.xls.Sheet3",A1))+1,99)&"-"&YEAR(TODAY()))) "Tom Ogilvy" bl... Filename is an argument to the Cell worksheet function telling it to return the fully qualified path and filename of the workbook (and the sheet name). However, it returns nothing unless the workbook has been saved once (so it has a fully qualified path and filename). -- Regards, Tom Ogilvy "jimmy" wrote in message ... Sorry, what is "filename" for? I tried to copy & paste your formula to E1 but doesn't, how to test? Thanks.. "Bob Phillips" .gbl... You could create data validation for the column, let's say column E for this example, with a custom type and a formula of =MONTH(E1)=MONTH(DATEVALUE("01-"&MID(CELL("Filename",A1),FIND("]",CELL("filename",A1))+1,99)&"-"&YEAR(TODAY()))) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "jimmy" wrote in message ... Hi all, How to set the entry in a specific column must in date type and checking for this date must in a specific month? e.g. in the sheet name "Feb", all the date in column in "A" other than February will not be accepted and prompt a dialog box to ask the user to use another sheet.. Thanks |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check month
Try
Worksheets(Format(Date,"mmmm")).Activate -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "jimmy" wrote in message ... ic..thank you. The 12 sheets have named as January to December, I wish to write the code in the Workbook open() function that the current month sheet activate...I tried MONTH(TODAY()).ACTIVATE but failed. What the code should be? "Bob Phillips" bl... This will vary the validation based upon the sheet name, so you can apply the same formula to all sheets, not a different one to each. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "jimmy" wrote in message ... Thank you.. In fact, what's the different between using this and just set 01-03-2007 to 31-03-2007 in the Data = Validation? "Tom Ogilvy" bl... First, the sheet must have a Month name, so change Sheet3 to Mar Put this in cell E1 of the sheet named Mar (don't change the formula - don't replace "filename") =MONTH(DATEVALUE("01-"&MID(CELL("Filename",A1),FIND("]",CELL("filename",A1))+1,99)&"-"&YEAR(TODAY()))) Now select Column A of the sheet named Mar go to Data=Validation Select Custom and in the textbox put in the formula =Month(A1)=$E$1 and click OK. -- Regards, Tom Ogilvy "jimmy" wrote in message ... My excel file is in the d:Book1.xls, I type the following in the sheet3 cell "E1", then E1 display 1900/1/0. How to use it to check for the cell in "A1" is my specific valid month? =MONTH(E1)=MONTH(DATEVALUE("01-"&MID(CELL("d:Book1.xls.Sheet3",A1),FIND("]",CELL("d:Book1.xls.Sheet3",A1))+1,99)&"-"&YEAR(TODAY()))) "Tom Ogilvy" bl... Filename is an argument to the Cell worksheet function telling it to return the fully qualified path and filename of the workbook (and the sheet name). However, it returns nothing unless the workbook has been saved once (so it has a fully qualified path and filename). -- Regards, Tom Ogilvy "jimmy" wrote in message ... Sorry, what is "filename" for? I tried to copy & paste your formula to E1 but doesn't, how to test? Thanks.. "Bob Phillips" .gbl... You could create data validation for the column, let's say column E for this example, with a custom type and a formula of =MONTH(E1)=MONTH(DATEVALUE("01-"&MID(CELL("Filename",A1),FIND("]",CELL("filename",A1))+1,99)&"-"&YEAR(TODAY()))) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "jimmy" wrote in message ... Hi all, How to set the entry in a specific column must in date type and checking for this date must in a specific month? e.g. in the sheet name "Feb", all the date in column in "A" other than February will not be accepted and prompt a dialog box to ask the user to use another sheet.. Thanks |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check month
Solved now. Thank you.
"Bob Phillips" bl... Try Worksheets(Format(Date,"mmmm")).Activate -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "jimmy" wrote in message ... ic..thank you. The 12 sheets have named as January to December, I wish to write the code in the Workbook open() function that the current month sheet activate...I tried MONTH(TODAY()).ACTIVATE but failed. What the code should be? "Bob Phillips" bl... This will vary the validation based upon the sheet name, so you can apply the same formula to all sheets, not a different one to each. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "jimmy" wrote in message ... Thank you.. In fact, what's the different between using this and just set 01-03-2007 to 31-03-2007 in the Data = Validation? "Tom Ogilvy" bl... First, the sheet must have a Month name, so change Sheet3 to Mar Put this in cell E1 of the sheet named Mar (don't change the formula - don't replace "filename") =MONTH(DATEVALUE("01-"&MID(CELL("Filename",A1),FIND("]",CELL("filename",A1))+1,99)&"-"&YEAR(TODAY()))) Now select Column A of the sheet named Mar go to Data=Validation Select Custom and in the textbox put in the formula =Month(A1)=$E$1 and click OK. -- Regards, Tom Ogilvy "jimmy" wrote in message ... My excel file is in the d:Book1.xls, I type the following in the sheet3 cell "E1", then E1 display 1900/1/0. How to use it to check for the cell in "A1" is my specific valid month? =MONTH(E1)=MONTH(DATEVALUE("01-"&MID(CELL("d:Book1.xls.Sheet3",A1),FIND("]",CELL("d:Book1.xls.Sheet3",A1))+1,99)&"-"&YEAR(TODAY()))) "Tom Ogilvy" bl... Filename is an argument to the Cell worksheet function telling it to return the fully qualified path and filename of the workbook (and the sheet name). However, it returns nothing unless the workbook has been saved once (so it has a fully qualified path and filename). -- Regards, Tom Ogilvy "jimmy" wrote in message ... Sorry, what is "filename" for? I tried to copy & paste your formula to E1 but doesn't, how to test? Thanks.. "Bob Phillips" .gbl... You could create data validation for the column, let's say column E for this example, with a custom type and a formula of =MONTH(E1)=MONTH(DATEVALUE("01-"&MID(CELL("Filename",A1),FIND("]",CELL("filename",A1))+1,99)&"-"&YEAR(TODAY()))) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "jimmy" wrote in message ... Hi all, How to set the entry in a specific column must in date type and checking for this date must in a specific month? e.g. in the sheet name "Feb", all the date in column in "A" other than February will not be accepted and prompt a dialog box to ask the user to use another sheet.. Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| excel to make the days cary over month to month automaticly | New Users to Excel | |||
| Excel 2003 month to month data change grid | Excel Discussion (Misc queries) | |||
| Number of days in month counted from shortened name of month & yea | Excel Worksheet Functions | |||
| copy worksheet from previous month and rename to current month | Excel Programming | |||
| transfer cell $ amount to other sheet month-to-month without overc | Excel Discussion (Misc queries) | |||
![jimmy[_5_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
