Forum Discussion
Complex data validation formula
I am trying to set up data validation for a cell that "Can" contain the date that the entry was posted to my financial institution.
There are 4 conditions that must be passed before the entered date is accepted.
The general purpose of this is two-fold First is to ensure that an entered date falls in the proper period. That's parts 1 and 2.
Parts 3 and 4 are to prevent posting of new transactions into dates that have already balanced. The current_Balanced_Date's are created on the 'Current Balances' sheet via a reverse Xlookup and place the "Newest" flagged date into the Current Balanced date for each account.
1 - The entered date can not be before the last day of the prior month ('Control Sheet'!Posting_Start_Date-1).
2 - The date can not be greater than the last date of the calendar month that it falls in ('Control Sheet'!Posting_end_Date)
3 - The Account of the transaction must be either "John" or "Pam".
4 - The entered date must be greater than the last reconciled date, by account, either, for account "John", 'Account Balances'!Johns_Current_Balance_Date or for account "Pam",'Account Balances'!Pams_Current_Balance_Date.
If there are any ideas I'llgladly accept the help.
TheOldPuterMan AKA John
(Happy Holidays and Peace to us all)!!
4 Replies
Sorry, meant to attach the file but it was late. Code in file DEPENDS on file name being in the current structure to work properly. It complains about external links. Ignore that as it is a problem I'm addressing that happened when I created the workbook from a larger one
Please specify the cell in which you need help with by manually entering your expected result and explaining its logic.
- You must first attach your sample file to obtain ideas; otherwise I seriously doubt you will get any.
