VBA & Macro Problems

How to Transfer a Total in Excel When a Cell is Blank Without Circular Reference

Bushra Parveen Sep 25, 2026 869 views

Question details

The user needs to carry a calculated total from one cell into a starting value cell automatically after clearing an input cell, without triggering a circular reference warning.

How to Transfer a Total in Excel When a Cell is Blank Without Circular Reference

Product: Microsoft Excel
Device & OS: not provided
Scenario: Managing daily inventory totals in a single row (e.g., starting with a value in B1, adding an input in A1 to get a total in C1), and needing C1's value to overwrite B1 when A1 is cleared for the next day.
Observed behavior: Attempting to use standard formulas causes a circular reference error because the starting cell (B1) and the total cell (C1) become dependent on each other.

Before you start

Before applying VBA solutions, ensure the Developer tab is enabled in your spreadsheet ribbon and save your workbook as a Macro-Enabled format (.xlsm) so the script can execute properly.

Solution 1Recommended

Use a Worksheet-Change VBA Macro

Implement a background VBA macro that automatically copies the total value to the starting cell when the input cell is cleared, bypassing formula-based circular reference limitations.

Formulas alone cannot resolve this because Excel prevents a cell from depending on its own calculation loop. A VBA Worksheet_Change event can act as a trigger, updating the values statically only when you specifically clear the target input cell.

This script turns off background events temporarily to prevent the macro from continuously re-triggering itself.

Access the VBA Editor

Open your workbook, navigate to the Developer tab on the ribbon, and click 'Visual Basic' (or press Alt + F11). In the Project Explorer on the left, double-click the specific worksheet (e.g., Sheet1) where your data resides.

Insert the Event Code

Copy and paste the following VBA code into the code window: Private Sub Worksheet_Change(ByVal Target As Range) If Target.CountLarge > 1 Then Exit Sub If Target.Address <> "$A$1" Then Exit Sub If Target.Value <> "" Then Exit Sub Application.ScreenUpdating = False Application.EnableEvents = False Application.Undo Range("B1").Value = Range("C1").Value Range("A1").ClearContents Application.EnableEvents = True Application.ScreenUpdating = True End Sub

Test the Macro

Close the VBA editor. Type your starting value in cell B1 (e.g., 100) and your daily input in cell A1 (e.g., 10). Ensure cell C1 has your sum formula =B1+A1. Now, delete the contents of A1. Cell B1 should immediately update to 110.

Save as Macro-Enabled

Go to File > Save As, and choose 'Excel Macro-Enabled Workbook (*.xlsm)' from the format dropdown list to ensure your VBA code is preserved.

Undo Functionality Warning: Executing a VBA macro, such as clearing the contents of cell A1, will automatically clear your Undo stack in Excel. You will not be able to undo actions taken prior to triggering the macro.

Solution 2

Use Multiple Rows for Historical Tracking

Structure your spreadsheet to use a new row for each day instead of overwriting a single row, entirely avoiding circular references while maintaining a history of changes.

Advanced Spreadsheet Capabilities

Automate Workflows Flawlessly in WPS Spreadsheet

WPS Office Spreadsheet provides excellent support for VBA macros and advanced formulas. You can seamlessly run, edit, and save macro-enabled files to automate tasks like transferring totals without circular references.

1. Open Your Workbook: Launch WPS Spreadsheet and open your existing file.
2. Enable the Developer Tools: Navigate to the 'Developer' tab in the top ribbon and click on 'Macro' or 'Visual Basic' to access the integrated coding environment.
3. Apply the Worksheet Change Script: Double-click your target sheet on the left panel and paste the VBA automation code.
4. Save and Execute: Save your file in the .xlsm format. The background automation will now run smoothly every time you clear the specified cell.

Fully compatible with Microsoft Excel macro-enabled workbooks (.xlsm)Built-in VBA editor for straightforward script management and automationLightweight application layout ensures high performance during heavy calculations

microsoft office alternative - wps office

Frequently Asked Questions

What is a circular reference in Excel?

A circular reference occurs when a formula directly or indirectly refers to its own cell to calculate a result. This creates an infinite loop that Excel cannot resolve, which is why you cannot make a starting total cell and an ending total cell depend on each other with standard formulas.

Why does running a macro clear my Undo history?

When a VBA macro makes a change to a worksheet, it fundamentally alters the environment and clears the Undo stack. This is a built-in limitation of Excel and VBA, meaning you cannot use Ctrl+Z to undo the specific actions performed by the script or actions done immediately prior.

Can I transfer totals automatically without VBA?

If you strictly use a single-row layout that constantly overwrites itself, VBA is mandatory. However, if you adapt your sheet layout to use a new row for each day, you can achieve automatic total transfers using simple formulas referencing the cell above it.

Excel & Data Problems