Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data validation with EOMONTH function
I have the following columns:
C D E StartDate EndDate ContractEndDate Columns C and D are user-entered fields; Column E is locked. In order to minizize data entry, I am using E0MONTH in cell D1 to autopopulate the end date of the value entered into cell C1: =EOMONTH(C1,0) Ninty-five percent of the time the value in column D will indeed be the last day of the month of the date entered in column C. However, in some instances the contract end date in column E is a mid-month date and in these instances I want to prevent the value in column D from being later than the value in column E. (In other words, if ContractEndDate is June 15th and StartDate is June 1st, I want to prevent EndDate from autofilling June 30th; in these instances I would like a stop warning reminding my users to overwrite the formula in D1 and manually enter the correct EndDate.) I put data validation in D1 requiring a value equal to or less than the value in E1. The validation works if I manually enter a value in D1 (overwriting the formula) but is ignored when D1 is autopopulated based on the value of C1. Is there a way I can get both end of month autopopulation AND data validation? Work arounds are appreciated. Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data validation with EOMONTH function
Could you achieve this with just a formula in D1? =if(EOMONTH(C1,0)<=E1,EOMONTH(C1,0),"ENTER DATE HERE") -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=523967 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
Case Sensitivity problem with data validation | Excel Worksheet Functions | |||
Using INDIRECT function to specify source data | Charts and Charting in Excel | |||
Effect of Conditional Formatting, Data Validation | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |