Restrict Values

MichaelWayne_71

Board Regular
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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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))

That helps

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

Thanks

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.

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.

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! ...

and, thank you for the help!

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?

Replies
1
Views
163
Replies
3
Views
226
Replies
19
Views
923
Replies
1
Views
467
Replies
2
Views
258

1,211,772
Messages
6,103,878
Members
447,883
Latest member
Hgiang0101

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.

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

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