ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Setting up and Configuration of Excel (https://www.excelbanter.com/setting-up-configuration-excel/)
-   -   Value Limits in a Cell (https://www.excelbanter.com/setting-up-configuration-excel/138421-value-limits-cell.html)

frankjh19701

Value Limits in a Cell
 
I need to be able to set a limit on a cell value and be able to warn if the threshold is about to be breached. For example:

If the maximum limit for a cell is 5,000 and it goes past 5,000, a warning message would be displayed alerting the limit being surpassed.
The cell is updated from another worksheet, so the value being added into it is not controlled from this spreadsheet (It's being referenced in). But, the warning should be seen from either the first spreadsheet (Where the value is being entered and then referencing the cell in question in another sheet), or in the spreadsheet where the cell is. Both would be great, if that's possible. Thank you in advance for any/all help in this matter.

Gary''s Student

Value Limits in a Cell
 
Consider using conditional formatting. It can be applied to both the cell
you desire to limit as well as the precedent cell.
--
Gary''s Student - gsnu200714


"frankjh19701" wrote:


I need to be able to set a limit on a cell value and be able to warn if
the threshold is about to be breached. For example:

If the maximum limit for a cell is 5,000 and it goes past 5,000, a
warning message would be displayed alerting the limit being surpassed.

The cell is updated from another worksheet, so the value being added
into it is not controlled from this spreadsheet (It's being referenced
in). But, the warning should be seen from either the first spreadsheet
(Where the value is being entered and then referencing the cell in
question in another sheet), or in the spreadsheet where the cell is.
Both would be great, if that's possible. Thank you in advance for
any/all help in this matter.




--
frankjh19701


frankjh19701

Could you give me an example, please?

Quote:

Originally Posted by Gary''s Student (Post 479094)
Consider using conditional formatting. It can be applied to both the cell
you desire to limit as well as the precedent cell.
--
Gary''s Student - gsnu200714


"frankjh19701" wrote:


I need to be able to set a limit on a cell value and be able to warn if
the threshold is about to be breached. For example:

If the maximum limit for a cell is 5,000 and it goes past 5,000, a
warning message would be displayed alerting the limit being surpassed.

The cell is updated from another worksheet, so the value being added
into it is not controlled from this spreadsheet (It's being referenced
in). But, the warning should be seen from either the first spreadsheet
(Where the value is being entered and then referencing the cell in
question in another sheet), or in the spreadsheet where the cell is.
Both would be great, if that's possible. Thank you in advance for
any/all help in this matter.




--
frankjh19701


tarquinious

Quote:

Originally Posted by frankjh19701 (Post 962643)
Could you give me an example, please?

On the first cell (the cell where the data is input) you could use Data Validation.

To do this (in Excel 2007):
- Select the cell that the value is input into
- On the Data toolbar, select Data Validation
- On the Settings tab, enter Allow: Whole Number, Data: less than, Maximum: 5000
- On the Error Alert tab, enter the error message you want to appear when a value greater than 5000 is entered.

Now that it is impossible to enter a value greater than 5000, there is no need to display anything on the second sheet as the value will never go over 5000.

If you would prefer Conditional Formatting however, I am happy to give an example of this instead.


All times are GMT +1. The time now is 11:51 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com