Forum Discussion
Data Validation Conditional Formating
I have a shared spreadsheet where coordinators enter staff codes for particular tasks they would like them to do (it's not a timesheet, just a future planning tool). I have set up a data validation to warn them if they enter a staff code that is not in the list, however if someone leaves after their code has been entered, the validation doesn't re-check so the now-invalid code remains happily in the spreadsheet. I need the cells to highlight if the staff code is not in the list.
Does anyone know if this is possible?
Currently, I need to search for each code as someone leaves and manually remove them from the spreadsheet and then ask coordinators to re-enter new values. It would be great if it would just flag them in red when I remove them from the staffing list and coordinators can see if there is (now) an error that needs fixing.
Not sure how to attach a file as it's not 'link'able with a URL, so here's screen shots:
user interfaceformulas to calculate the total hours based on the task typeconditional formatting to highlight the table on the right, or when someone does a seazrch for one particular staff member using the box below the 1st table
You mean something like this?
Adjust the 'Applies to' ranges to your specific needs.
4 Replies
Yes! Thankyou
This is the rule for conditional formatting in the attached sample file.
=(B3=$A$17)*ISNA(MATCH(B3,$H$3:$H$17,0))This is the applies to range for conditional formatting.
=$B$3:$B$14;$D$3:$D$14;$F$3:$F$14In cell A17 i've added Data Validation without a dropdown.
In the screenshot all cells containing "July" in ranges $B$3:$B$14;$D$3:$D$14;$F$3:$F$14 are highlighted in red after "July" was removed from range H3:H17.
This just highlights whatever is in cell A17 as red. If I delete something from the list (eg Wednesday), nothing happens. I'm not sure what the ISNA match stuff does, but I've achieved the same result using cell value = $A$17 conditional formatting. It's still not what I want though. Thanks for trying.
You mean something like this?
Adjust the 'Applies to' ranges to your specific needs.
