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!
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
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,210

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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,994
Messages
5,834,773
Members
430,320
Latest member
Napzz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top