How to Automatically Record Excel Formula Inputs and Results in Another Sheet
Question details
The user wants to use an Excel macro to automatically log a specific cell's input value and its corresponding formula-calculated result onto a different worksheet without overwriting previous historical records.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- A user enters data into an input cell which triggers a formula. They need a continuous, automated record of every input and its resulting output stored sequentially row-by-row on a separate tracking worksheet.
- Observed behavior
- The intended goal is that whenever the input cell is changed, the macro immediately copies both the input value and the formula result as static values to the next available empty row on the target destination sheet.
Ensure your workbook is saved as an Excel Macro-Enabled Workbook (.xlsm) and that you have enabled macros in your spreadsheet application's Trust Center settings before writing and executing VBA code.
Use a Worksheet_Change VBA Event
This method utilizes a VBA event macro tied to the worksheet to monitor a specific input cell. Once the cell is updated, the script automatically copies the input and the formula result to the next available empty row on a separate tracking sheet.
By utilizing the Worksheet_Change event, you can tell Excel to execute code the moment a user finishes typing in a specific cell. To ensure that only historical data is kept, the macro must copy the '.Value' of the cells rather than the formulas themselves.
It is crucial to correctly identify the next empty row on your target sheet so that previous logs are preserved. This is typically done by finding the last used row in a specific column and adding one to it.
Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.
In the Project Explorer pane on the left, double-click the worksheet where your input cell is located (for example, Sheet1).
Select 'Worksheet' from the left dropdown menu and 'Change' from the right dropdown menu above the code window. Write an IF statement checking if 'Target.Address' matches your input cell (e.g., $A$1).
Within the IF block, declare a variable for the next empty row. Use a statement like 'NextRow = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row + 1' to find the next available slot on your tracking sheet.
Assign the values to the target sheet. For example: 'Worksheets("Sheet2").Cells(NextRow, 1).Value = Target.Value' to copy the input, and 'Worksheets("Sheet2").Cells(NextRow, 2).Value = Range("B1").Value' to copy the formula result.
Close the VBA editor. Enter a new value in your monitored cell on Sheet1, then switch to Sheet2 to verify that the input and calculated result were logged in a new row.

Automate Data Logging with WPS Spreadsheet
WPS Office Spreadsheet provides robust support for macros and VBA, allowing you to easily automate tasks like recording formula inputs and outputs sequentially to a secondary log sheet.
- 1. Open your Workbook in WPS Office: Launch WPS Spreadsheet and open your existing Excel workbook containing the formulas.
- 2. Access the Developer Tab: Go to the 'Developer' tab on the top ribbon and click 'VBA Editor' to open the scripting window.
- 3. Apply the Tracking Code: Double-click your input sheet in the project pane, paste your Worksheet_Change event macro to record the values, and save your workbook as a Macro-enabled file.

Frequently Asked Questions
Why is my VBA macro overwriting the same row instead of moving to the next?
This happens if the macro cannot correctly identify the last used row dynamically. Ensure you are using the code snippet `Cells(Rows.Count, 1).End(xlUp).Row + 1` to find the first empty row rather than hardcoding a specific row number.
Can I record multiple input cells at once?
Yes. You can modify the Worksheet_Change event to monitor an intersection of a designated range using `Intersect(Target, Range("A1:A10"))`. You can then loop through the changed cells to log multiple inputs simultaneously.
Will the macro copy the formula or just the calculated result?
To ensure your history log remains static and accurate, your VBA code should be set to copy the `.Value` property. If you copy the entire cell object, the formula itself will be transferred and it will attempt to recalculate on the new sheet based on relative references.
Why isn't my Worksheet_Change event triggering when I type?
First, ensure macros are enabled in your Trust Center settings. If they are, check if `Application.EnableEvents = False` was accidentally triggered by a previous macro and never reset. If so, you will need to run a quick script containing `Application.EnableEvents = True` to restore background event monitoring.




