Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I set a limit in a cell
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I set a limit in a cell
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" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I set a limit in a cell
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I set a limit in a cell
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" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I set a limit in a cell
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" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I set a limit in a cell
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" wrote in message ... 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" wrote in message ... 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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I set a limit in a cell
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" wrote in message ... 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" wrote in message ... 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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I set a limit in a cell
-- HTH, Bernie MS Excel MVP "Kim" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I set a limit in a cell
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I set a limit in a cell
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to I set a limit on the value returned by a cell | Excel Worksheet Functions | |||
How to limit the characters in a cell | Excel Worksheet Functions | |||
How to limit cell to 30 characters or less | Excel Discussion (Misc queries) | |||
Is there a character limit within a cell? | Excel Discussion (Misc queries) | |||
Nested IF limit or Open parentheses limit | Excel Discussion (Misc queries) |