data validation & progress report

[chahine]

Hello excel community

I am creating progress report, so i have one challenge which am not able to do, dont know if its possible

1. When i am entering progress for certain activity, i want excel to warn the user that he entered wrong value, for example for room 1 activity fire alarm if the progress on date 15-9-22 was 10 %, so the next time when the user enters for room 1 activity fire alarm on date 17-9-22 ,5 % it should warn him that no this is wrong, progress cannot be less than last date

also if we have already progress for room 1 activity fire alarm on date 15-9-22 10%, and on 17-9-22 progress is 20% 

if a user wants to enter progress on date 16-9-22 he cannot enter more than 20% for example as already on date 17 it was 20%

its a bit complicated, but is it possible without vba? i tried maxif function for progress, but my challenge now is for dates 

i am attaching a sample file

thanks for any advise or hints

[#2]

@chahine 

See the attached version. I changed the data range into a table and used two defined names to refer to the Date and Progress columns.

[#3]

@Hans Vogelaar Thanks for reply

My issue is with dates, i want to check progress based on date, for example if my progress on 17/9/22 was 22% , and for some reason i entered a progress of 23% on 10-9-22 then it should give an error as progress on past date cannot be more than progress on future date, dont know if am clear enough, i put one example with yellow highlight in the file

[#4 · Accepted solution]

@chahine 

It turns out there was a problem if there is a new empty row below the cell where you enter/edit a percentage

In the attached version I added a helper column to get around it.

[#5]

Thanks Hans, it worked, ill try to tweak it so it works on all excel versions as MAXifs doesnt work on old excels like 2010