Date Validation

[Anthony Gregg]

I have a date column in a worksheet. The worksheet is will
contain a month of data. So I will have twelve worksheets
in a workbook containing data. I will have one workbook
per year.

So the problem I have is that I want to validate the date
entered into a worksheet as being valid for that month of
data entered into that one worksheet.

I have tried the Date Validate function that is part of
the Data Validation dialog, but it does not allow the Year
to be based on a variable. I need the year to be not fixed
because the life of this spreadsheet could span a number
of years.

I tried a custom formula in the Data Validation dialog. I
put in the following:

Say cell H1 is where I am putting my date value into and I
am entering into the June worksheet:

=AND(H1 > DATE(YEAR(TODAY()),6,1), H1 < DATE(YEAR(TODAY
()), 6, 30))

But of course I get a circular reference error as I am
comparing H1 with itself in the formula. So this won't
work either. Does anyone have an idea how to solve this?

Is there another way of checking the value in a cell to
see if it is valid?