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.

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


"Kim" <(E-Mail Removed)> wrote in message
news:8B1BC89E-969F-412C-9955-(E-Mail Removed)...
> 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.

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" wrote:

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

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" wrote:

> 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
>
>
> "Kim" <(E-Mail Removed)> wrote in message
> news:8B1BC89E-969F-412C-9955-(E-Mail Removed)...
> > 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.
>
>
>

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" wrote:

> 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
>
>
> "Kim" <(E-Mail Removed)> wrote in message
> news:8B1BC89E-969F-412C-9955-(E-Mail Removed)...
> > 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.
>
>
>

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" <(E-Mail Removed)> wrote in message
news:1C945000-43AF-45DF-B199-(E-Mail Removed)...
> 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" wrote:
>
>> 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
>>
>>
>> "Kim" <(E-Mail Removed)> wrote in message
>> news:8B1BC89E-969F-412C-9955-(E-Mail Removed)...
>> > 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.
>>
>>
>>

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" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:1C945000-43AF-45DF-B199-(E-Mail Removed)...
> > 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" wrote:
> >
> >> 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
> >>
> >>
> >> "Kim" <(E-Mail Removed)> wrote in message
> >> news:8B1BC89E-969F-412C-9955-(E-Mail Removed)...
> >> > 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.
> >>
> >>
> >>
>
>
>

--
HTH,
Bernie
MS Excel MVP


"Kim" <(E-Mail Removed)> wrote in message
news:7C8B3D52-C66E-496E-AA1B-(E-Mail Removed)...
> 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" wrote:
>
>> 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" <(E-Mail Removed)> wrote in message
>> news:1C945000-43AF-45DF-B199-(E-Mail Removed)...
>> > 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" wrote:
>> >
>> >> 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
>> >>
>> >>
>> >> "Kim" <(E-Mail Removed)> wrote in message
>> >> news:8B1BC89E-969F-412C-9955-(E-Mail Removed)...
>> >> > 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.
>> >>
>> >>
>> >>
>>
>>
>>

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" <(E-Mail Removed)> wrote in message
news:7C8B3D52-C66E-496E-AA1B-(E-Mail Removed)...
> 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" wrote:
>
>> 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" <(E-Mail Removed)> wrote in message
>> news:1C945000-43AF-45DF-B199-(E-Mail Removed)...
>> > 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" wrote:
>> >
>> >> 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
>> >>
>> >>
>> >> "Kim" <(E-Mail Removed)> wrote in message
>> >> news:8B1BC89E-969F-412C-9955-(E-Mail Removed)...
>> >> > 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.
>> >>
>> >>
>> >>
>>
>>
>>

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.

"Bernie Deitrick" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:7C8B3D52-C66E-496E-AA1B-(E-Mail Removed)...
> > 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" wrote:
> >
> >> 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" <(E-Mail Removed)> wrote in message
> >> news:1C945000-43AF-45DF-B199-(E-Mail Removed)...
> >> > 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" wrote:
> >> >
> >> >> 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
> >> >>
> >> >>
> >> >> "Kim" <(E-Mail Removed)> wrote in message
> >> >> news:8B1BC89E-969F-412C-9955-(E-Mail Removed)...
> >> >> > 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.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>