data validation with formulas
Posted by Matt T on January 23, 2002 11:08 AM
I have a cell (A1) that has a formula in that determines this value, this is counting values in another table (COUNTIF). I want to set the maximum of this value, so if I put the value becomes higher than the maximum, an error message appears.
Posted by Read your earlier post for answers first on January 23, 2002 11:09 AM
Please quit posting the same question over and over
Posted by Joe Was on January 23, 2002 11:45 AM
If your formula will be in cell D5, go to Data-Validation, select Settings, in Allow select Custom, in formula put =D5<=F5
F5 is the value of your limit.
Click OK. Then select your formula cell the one we just put the Validation on (D5) and enter your formula. You must have a true case to start with or your Validation will lick you out!
So if your formula has references to other Cells put good values in them!
Also, everyone who sees your note and who has the time to answer will, do not repost. Unless a few days has passed and you have not recieved an answer!
Your actions are the fastest way to get no-one to answer. JSW
Posted by Dan on January 24, 2002 6:04 AM
Actually Joe, unless I'm not understanding you correctly, that won't work. It will only work on the first time that you enter your formula. It won't work if the value of your formula changes afterwords because Excel doesn't recognize a recalculation of a formula as an event. The best way is to not use data validation on the cell with the formula in it, but instead use data validation on the cells that the formula references (where the data is actually inputted). I had answered Matt T's question in a previous post and was getting a little miffed that he kept asking the same thing over and over without bothering to look at his old post from 1/2 hour ago!
Anyways, let me know if I'm mistaken...
-Dan
