Excel 2007 : moving excess amount into different cell

**pharma1** · 02-27-2012, 02:03 PM

Hi All,

I am very new at Excell and could use some help. Is it possible to set at max number for a cell and have anything over that set number go to a different cell? An example would be as follows.

Cell A1 is set at 50. If I type in 67, A1 would stay at 50 and the other 17 would go to a cell of my choice.

and

Cell A1 is set at 50. Anything less than 50 would read that value. Say 35 just stays 35.

Thanks

**JieJenn** · 02-27-2012, 02:17 PM

I attached like a sample based on what you described in the post. Just follow the formula logic.

**SpotOn** · 10-21-2018, 09:06 PM

Thank you JieJenn…! That attached file had the exact formula that I needed, I had hunted for days seeking this calculation! With appreciation Thank You!

SpotOn

**Arki Gen** · 07-07-2023, 08:49 AM

This was very helpful. However what if there are multiple excess numbers that needs to be sum up into a different cell?

**tigeravatar** · 02-27-2012, 02:20 PM

pharma1,

Welcome to the forum!

What you're asking for can be accomplished a couple of ways. If you're okay with different cells changing, you can use formulas. If it has to be the cell you entered into changing, it would need to be a macro.

Attached is an example workbook that shows both solutions.

In the formula solution, you enter the number into cell A2. Cell B2 will show that number, maxing out at 50. Cell C2 will show the remainder (if any).
B2 formula: =MIN(A2,50)
C2 formula: =IF(A2<=50,"",A2-B2)

In the macro solution, you enter the number into cell A5. Cell A5 will remain that number if it is 50 or less, but will change to show the max, 50, if you enter a higher number. Cell B5 will show the remainder if any. This is accomplished using a Worksheet_Change event, and here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Address = "$A$5" Then
        Application.EnableEvents = False
        If Target.Value > 50 Then
            Target.Offset(, 1).Value = Target.Value - 50
            Target.Value = 50
        Else
            Target.Offset(, 1).ClearContents
        End If
        Application.EnableEvents = True
    End If
    
End Sub

**pharma1** · 02-27-2012, 03:21 PM

Thanks to the both of you. That will do exactly what I want. The macro was way over my head but the formulas made sense once I looked at them. I'm sure I will be here again for something else.

**AliGW** · 07-07-2023, 08:51 AM

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread