Pop Up Warnings though Data Validation

[cmcconnehey]

I’m building a template that lets the user flag a certain number of employee names in the spreadsheet. If the number of employees flagged exceeds the limit I need an error message to pop up and warn that the limit is passed.

I’ve tried unsuccessfully to achieve this through a combination of formulas. I have a count function determining the number of employees in the list (cell C19). I have another cell (H22) showing the number of employees eligible (50% of employees are eligible, so the cell rounds down to 7). Cell H19 counts how many of the “y” flags have been placed within the list of employees.

What I tried to do was set validation on the count of employees with flags (H19) so that it pops up a warning message if this value exceeds the number of employees eligible (H20). However, I changes the flags and nothing happens with validation. It appears that I’m required to enter text directly into the referenced cell containing the validation (H19) in order for it to set off the flag.

Is there a better way to achieve this pop up warning?

[rylo]

Hi

Unfortunately Excel won't recognise a recalculation of a formula as an event trigger for validation.

A couple of thoughts.
1) Use an event macro to build your error message.
2) Create a list from a dynamic named range. If the count of "y" in column H is <= H20, then it will show the list as containing the 2 values (ie blank and y), else it will only show the blank. That way the user cannot add another "y" when the limit has been reached.


HTH

rylo