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