How to allow value in cells overflow to another cell?

[Ray92]

​Hi guys,

I am just curious how can i work around the formula such as when A1 is "100" (this is the maximum value), B1 is "110" and the excess of "10" is overflow to C1, while B1 is kept to "100"?

I couldn't find it anywhere else that solves this.

Your help would be greatly appreciated.

Thank you!

 

[DanteAmor]

Try this code in the events of your sheet

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("B1")) Is Nothing Then
    If Target.Count > 1 Then Exit Sub
    If Target.Value > Target.Offset(, -1) Then
      Application.EnableEvents = False
      Target.Offset(, 1).Value = Target - Target.Offset(, -1)
      Target.Value = Target.Offset(, -1)
      Application.EnableEvents = True
    End If
  End If
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

 

[TheRogue]

Is there Input somewhere else? Are you saying that when "210" is entered, A maximum of 100 goes to each column?
Or are you saying that when you type "100" into A, B automatically gets populated w/ "100" & C w/ "10"?
I'm not sure what the question is. I can't be understanding it right, b/c I see no practical purpose.

 

[Ray92]

Hi TheRogue,

The "100" in A is the value I will keep there permanently, and B is the column I will work around with. When B is more than 100, the excess overflows to C.

 

[Ray92]

Hi DanteAmor,

Thanks for the code!

I am also working around with Google Sheets for education purposes.

Just wondering if you may know how can I work around with it too?

Thank you!

 

[Rick Rothstein]

When B is more than 100, the excess overflows to C.

The user is entering a number in cell B1, correct?

Assuming a number larger than 100 is put into cell B1... if the overflow is going into cell C1, D1, etc. as needed, then is the value in cell B1 to be changed to 100?

 

[Ray92]

Hi Rothstein,

Yes, you are right!

I am just wondering if there's a formula to this?

Thank you!

 

[DanteAmor]

Hi DanteAmor,

Thanks for the code!

I am also working around with Google Sheets for education purposes.

Just wondering if you may know how can I work around with it too?

Thank you!

I'm glad to help you.
I have no way to try google sheets. Good luck

 

[Rick Rothstein]

The user is entering a number in cell B1, correct?

Assuming a number larger than 100 is put into cell B1... if the overflow is going into cell C1, D1, etc. as needed, then is the value in cell B1 to be changed to 100?

Hi Rothstein,

Yes, you are right!

I am just wondering if there's a formula to this?

A cell cannot contain both typed in text and a formula, so the only way someone can type a value into cell B1 and have it change to 100 if the typed in number is larger than 100 is by using VBA event code (for it to happen immediately) or a macro (for it to happen at the user's discretion). By the way, this is the event code that I would use...

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim OverFlow As Long, CellsWith100 As Long
  On Error GoTo Oops
  Application.EnableEvents = False
  If Target.Address(0, 0) = "B1" And Len(Range("B1").Value) > 0 Then
    Range("C1", Range("B1").End(xlToRight)).ClearContents
    OverFlow = Target.Value Mod 100
    CellsWith100 = Int(Target.Value / 100)
    Range("B1").Resize(, CellsWith100).Value = 100
    Range("B1").Offset(, CellsWith100).Value = OverFlow
  End If
Oops:
  Application.EnableEvents = True
End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

I am also working around with Google Sheets for education purposes.

Just wondering if you may know how can I work around with it too?

I have never used Google Sheets, so I have no idea how to do what you asked for there.

 

[Ray92]

Thank you for the coding!