Formulas are appearing without any macros or VBA-CodePudding

Here is a little puzzle. The supplied sheet contains 2 columns of 4 rows each.

If you enter a value in the column marked as such, a formula is created in the magic column. The formula itself is not remarkable, as it's just the value you entered 8. The remarkable part is how this formula finds its way into the cell when you enter a value in the adjacent cell.

You need to open the file in Excel.

Can you figure out how it works?

Things we think we've established:

There is no VBA code, there are no macros, there is no XML.
You need 4 rows of magic for magic to continue
You can delete any rows as long as there are 4 rows of magic remaining
You can clear rows and insert rows, but you may not alter rows such that there are fewer than 4 rows of magic remaining.
It works in both xlsm, xlsb, xlsx and xls.
Works in Excel 365 and Excel 2003.
There are no objects. There are no tables. There are no hidden sheets or very hidden sheets.
There is no formatting of any kind.
It breaks if you transpose it.
It breaks if you view the xlsx in Google Sheets.
It still works with calculation set to manual.
You can copy the range to a new sheet or workbook as long as you copy 4 or more rows of magic.
The rows you copy do not have to be the original rows supplied in this sheet. You can create your own rows by entering data, then copy your new ones as long as you have 4 or more rows.
It works with all forms og AutoFill and AutoComplete turned off.
By altering the magic column as described above you change the formula that appears and it breaks if it doesn't have 4 functioning rows of magic.
There is nothing else hidden in the sheet, no rows or columns.
The column references do not appear to be hardcoded.

Here's the file: https://docs.google.com/spreadsheets/d/1cSbHV5prWzmIPPrzeQSR5nWOdcnmPmqf/edit?usp=sharing&ouid=105488412305966404082&rtpof=true&sd=true

Best of luck.

CodePudding user response：

I also tried working this out with him for HOURS and we have no idea how this old spreadsheet we found is being powered.

CodePudding user response：

This is standard Excel behaviour. It is controlled by the option "Extend data range formats and formulas":

Select to automatically format new items added to the end of a list to match the format of the rest of the list. Formulas that are repeated in every row are also copied. To be extended, formats and formulas must appear in at least three of the five last rows preceding the new row.

Source: https://support.microsoft.com/en-us/office/advanced-options-33244b32-fe79-4579-91a6-48b3be0377c4