How can I set a limit in a cell

[Kim]

Can someone please help me with the problem below. I need a formula where I
can set a limit of figure I can input in a cell.

Example:

A B C D E
1 50
2 20
3

I want to set cell A1 to A3 that the total input cannot be more than 100. If
i enter 50 on A1 and 20 in A2 and in cell A3, they can only enter between 0 -
30.

Thanks.

 

[Bernie Deitrick]

Kim,

Select cell A3, the use Data / Validation... choose "Custom" and use the formula

=AND(A3>=0,A3<=(100-A1-A2))

HTH,
Bernie
MS Excel MVP

 

[francis]

do you allow input only 30 in A3 from your example or
can others input a lesser number, say 20, which doesn't
exceed a total of 100?

Click on A3
only the differences between the sum of A1:A2 and the total of 100 allow
Data >> Validation
Allow >> choose Whole Number
Data >> choose between
Minimum >> =100-(SUM(A1:A2))
Maximum >> =100-(SUM(A1:A2))
OK

if a lesser number can be input
Data >> Validation
Allow >> choose Whole Number
Data >> choose between
Minimum >> 0
Maximum >> =100-(SUM(A1:A2))
OK

--
Hope this is helpful

Click the Yes button below if this post work for you.


Thank You

cheers, francis

 

[Kim]

Sorry. Maybe my question wasn't clear.

What I'm trying to do is between cell A1 and A3, you can put a maximum of
100 only.

Ie - If cell A1 is 10 and A2 is 50. Then the maximum I can enter in cell A3
is 40
If cell A2 is 50 and cell A3 is 20 then I can only enter max of 30

Regards,

 

[Kim]

Sorry. Maybe my question wasn't clear.

What I'm trying to do is between cell A1 and A3, you can put a maximum of
100 only.

Ie - If cell A1 is 10 and A2 is 50. Then the maximum I can enter in cell A3
is 40
If cell A2 is 50 and cell A3 is 20 then I can only enter max of 30

Regards,

 

[Bernie Deitrick]

You need to use data validation on all three cells.

For A1: =AND(A1>=0,A1<=(100-A2-A3))
For A2: =AND(A2>=0,A2<=(100-A1-A3))
For A3: =AND(A3>=0,A3<=(100-A1-A2))

HTH,
Bernie
MS Excel MVP

 

[Kim]

Hi Bernie,

Thanks. That's almost what I wanted. The only issue is the entry to cell A1
to A3 is not from entering directly to the cell, but from a scroll bar.

Example
If i move the scroll bar (maybe in cell B1) then that number will be appear
in cell A1
There will be another scroll bar in cell B2 to control the value in B2.

So basically when I move all the scroll bar, it the total should not be more
than 100. If that can be achieved, that would be great !!

But thanks for your help so far.

 

[Bernie Deitrick]

--
HTH,
Bernie
MS Excel MVP

Hi Bernie,

Thanks. That's almost what I wanted. The only issue is the entry to cell A1
to A3 is not from entering directly to the cell, but from a scroll bar.

Example
If i move the scroll bar (maybe in cell B1) then that number will be appear
in cell A1
There will be another scroll bar in cell B2 to control the value in B2.

So basically when I move all the scroll bar, it the total should not be more
than 100. If that can be achieved, that would be great !!

But thanks for your help so far.

 

[Bernie Deitrick]

Kim,

Try this, assuming your scroll bars are named Scroll Bar 1 for A1, Scroll Bar 2 for A2, Scroll Bar 3
for A3:

In a regular module, put

Option Explicit

Public myR As Range

Sub ScrollBarMaxMacro()
Application.EnableEvents = False
ActiveSheet.Shapes("Scroll Bar 1").Select
Selection.Max = 100 - Range("A2").Value - Range("A3").Value
ActiveSheet.Shapes("Scroll Bar 2").Select
Selection.Max = 100 - Range("A1").Value - Range("A3").Value
ActiveSheet.Shapes("Scroll Bar 3").Select
Selection.Max = 100 - Range("A1").Value - Range("A2").Value
myR.Select
Application.EnableEvents = True
End Sub

In the worksheet module, use

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set myR = Target
End Sub

Then assign the macro ScrollBarMaxMacro to each of the three scroll bars. This assumes that cells
A1:A3 start out blank.


HTH,
Bernie
MS Excel MVP

 

[Kim]

I'm not any good in Macro so can't test it out. I'll probably ask my other
colleague if they know how to set the macro. Unless you have other formulas.

 

[Pete_UK]

Suppose your points entered are in A2 and available points are in B2,
then you could do this in C2:

=IF(B2=0,0,MIN(A2/B2,1))

and format the cell as percentage.

Hope this helps.

Pete