Restrict Values

MichaelWayne_71

Board Regular
Joined
Nov 9, 2005
Messages
60
Is there a way to restrict a cell value from being higher than the sum of a range of cells?

Thank you!!!

I love this message board!
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
MichaelWayne_71 said:
Is there a way to restrict a cell value from being higher than the sum of a range of cells?

Thank you!!!

I love this message board!

If got it right...

You could invoke in a data validated cell, say, A2...

=ISNUMBER(A2)*(A2<=SUM($B$2:$F$2))
 

MichaelWayne_71

Board Regular
Joined
Nov 9, 2005
Messages
60
That helps

that helps...but, not sure what you mean by data validated cells. Can you elaborate?

Thanks
 

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
Select Cell A2.
Go to Data, Validation from the menus.

Select Allow: Custom
In the Formula dropdown, enter: =ISNUMBER(A2)*(A2<=SUM($B$2:$F$2))
Click OK.

Adjust the range to suit how much data you have. Hope that helps!

Nice one, Aladin! I would have gotten that one by myself...eventually. The ISNUMBER is something I wouldn't have thought of, though.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

Re: That helps

MichaelWayne_71 said:
that helps...but, not sure what you mean by data validated cells. Can you elaborate?

Thanks

If you don't know what data validation is, my suggestion cannot be of much help. Lets leave this for a moment and try to elaborate instead on the problem you want to solve by means of an example.
 

MichaelWayne_71

Board Regular
Joined
Nov 9, 2005
Messages
60
Example

I have a quote for a product which has a certain number of items bundled in the price. Users can add-on items to that product. However, if they go over a certain #, another item has to be added.

Hope that helps! :biggrin:...

and, thank you for the help!
 

MichaelWayne_71

Board Regular
Joined
Nov 9, 2005
Messages
60
Better Yet

I have a cell that others look at for their value. Can I make THAT cell look at a range and add to it, but not affect the cells that are looking at it?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,084
Messages
5,570,131
Members
412,305
Latest member
Mozz
Top