Hey everyone,
Essentially I am trying to validate inputted data but the Excel 'data validation' features seems too limited. Let me know if I am wrong.
I export sets of data of different location and date ranges and I would like to track which locations and date ranges have been exported to ensure that user exported data doesn't accidentally overlap.
For data that is about to be exported, the date range and location data is found in three cells, temp1!M3, temp1!N3, temp1!O3.
Date range:
Date from(cell temp1!M3): =min(temp1!F:F)
Date to (cell temp1!N3): max(temp1!F:F)
(where column F is where the dates are for the data that is to be exported)
Location:
(cell temp1!O3): =temp1!S2
So far, what I've done is create a macro which copies the FROM Date, TO Date and LOCATION cells each time data is exported to the next available row in the following locations respectively: ref!P, ref!Q, ref!R . Now I am not sure what to design to be able to detect if users are trying to export data which contains dates that overlap with a date range for a particular location that have been recorded as already exported. I expect users to export on a weekly basis (for data covering a week but never to included weekends or holidays).
Any ideas? So far, I've tried using a formula like this one in the data validation tool for the cell temp!O3 (the cell which contains the location) : =COUNTIFS(ref!P:P,"<="&temp1!M3,ref!Q:Q,">="&temp1!N3,ref!R:R,temp1!O3)<=1
but it doesn't seem to work, and I'm not sure if the formula actually does what I want it to do.
Please help!
Goeff.
Bookmarks