Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation on Locked Cell
First, I would like to know if I can provide data validation on a cell that
has a formula in it (i.e. a cell that will not have a user enter a value). If not, then I'm sol. If not, here's my scenario: I have a cell that I do not want to calculate to over 450. There is a formula in this cell that calculate 5 other summary cells. What I want to do is setup validation so that if the calculation of this cell goes over 450 a Stop error comes up. I know I can do this by setting validation rules for all of the cells I will enter data into, but that's over 100 cells that already have their own validation. I just want to know if there is a way to set this validation rule on a cell that will have a formula in it and is locked so users cannot enter data. Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation on Locked Cell
Data|validation is to stop the user from typing something invalid.
How about using an adjacent cell with a formula like: =if(a1450,"Error!","") robbyp wrote: First, I would like to know if I can provide data validation on a cell that has a formula in it (i.e. a cell that will not have a user enter a value). If not, then I'm sol. If not, here's my scenario: I have a cell that I do not want to calculate to over 450. There is a formula in this cell that calculate 5 other summary cells. What I want to do is setup validation so that if the calculation of this cell goes over 450 a Stop error comes up. I know I can do this by setting validation rules for all of the cells I will enter data into, but that's over 100 cells that already have their own validation. I just want to know if there is a way to set this validation rule on a cell that will have a formula in it and is locked so users cannot enter data. Thanks. -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation on Locked Cell
Validation applies only to values typed in by the user. I has no
effect on calculated results. You could use Conditional Formatting to change the cell to red if some value is exceeded. Another way to prevent the value of a cell from going above some value is to use the MIN function: =MIN(your_formula,MaxAllowableValue) Here, the result will be the lesser of your formula and the MaxAllowableValue value, 450 in your case. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Tue, 20 Jan 2009 10:47:01 -0800, robbyp wrote: First, I would like to know if I can provide data validation on a cell that has a formula in it (i.e. a cell that will not have a user enter a value). If not, then I'm sol. If not, here's my scenario: I have a cell that I do not want to calculate to over 450. There is a formula in this cell that calculate 5 other summary cells. What I want to do is setup validation so that if the calculation of this cell goes over 450 a Stop error comes up. I know I can do this by setting validation rules for all of the cells I will enter data into, but that's over 100 cells that already have their own validation. I just want to know if there is a way to set this validation rule on a cell that will have a formula in it and is locked so users cannot enter data. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validation Data using Validation Table cell range..... | Excel Discussion (Misc queries) | |||
locked cell not locked | Excel Discussion (Misc queries) | |||
Locked Cells with Data Validation Drop Down Lists | Excel Discussion (Misc queries) | |||
Why does data validation not work when pasting data into a cell. | Excel Discussion (Misc queries) | |||
Can validation input messages be locked in place? | Excel Discussion (Misc queries) |