Thanks for the reply. So this is what I have:
This means that the status page shows the date and location of the next trip which is copied from the table listing the trip plan. If today's date is the date of the trip, then it moves date_ref by 1 (via H1) and replaces the date/location so that it is now displaying the data for the next trip.
As far as I can tell, that all works perfectly. You would have to check the previous night if the trip is going ahead as once you hit midnight, it will begin displaying for the next trip (not the one happening that day) - but that's a procedural thing and not a problem.
The problem I am having now is this:
I have one column which will have a number in it, which is the number of times that the criteria for the trip have been broken (it may be broken more than once per record made). I am unsure how to construct the IF statements to look at that column which also maintaining the criteria of being between the two dates - the problem is, how do I get the IF statement to consider a vast range of cell references (as I'll want to it compare all the possible dates, which will number in the hundreds). Something like:
If (cells A1:A3000) >= (date of last trip) AND (cells A1:A3000) < (Date of next trip) Then
Sum of (Criteria column) between those two dates
If (Sum of criteria column) > 1 Then '(if it totals more than 1, then the allowance was used up and then critieria broken again)
Worksheet A (cell which says if criteria was broken more than once) = Yes '(therefore making the leave state = Not Achieved)
Else
Worksheet A (cell for criteria) = No '(therefore making the state = Currently Achieved)
it's the first three lines I'm not sure what the exact syntax should be to get it to consider all the different If criteria in the right order.
Thanks again.
Bookmarks