Forum Discussion
data validation & progress report
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
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.
4 Replies
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.
HansVogelaar 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
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.
