dynamic arrays turn off

[dankuchma]

I am panicking because I really need to turn off the new dynamic array feature or I am ........ . I will illustrate this in an example and hope that there is a fix out there. Consider that I created an array in cells A1:A4 in which the name of the array is given in cell A1 as "value". This would appear as given below, and I could then created the array by highlighting cells A1:A4 and selecting "Alt-I+N+C" for Insert-Name-Create and selecting "top-row" for the location of the name. 

A1 = value

A2 = 5

A3 = 8

A4 = 12

It used to be that if I went to cell B3 (or C3, D3, etc.) and entered "=value" that it would return the value "8".  If I do this now, then it puts the values of 5, 8, and 12 in cells B3 through B5. 

I have many thousands of hours of spreadsheet work invested in which I have relied on the use of arrays behaving like they used to. It is not uncommon that I have hundreds of defined arrays, each with several hundred entries, and then formulas with a dozen or more variables. These are used for making engineering design and analysis calculations. I did try saving the file as an older worksheet, but this did not help. 

I hope that there is a fix out there.

[#2]

Instead of entering =value, enter =@value.

[#3]

Thanks, this worked. I noticed that I have to do this for every variable in my expression so the equations are much less clear than in the past. I am not sure how old excel documents will be adjusted. I hope that Microsoft provides an option to turn this off.

@Jan Karel Pieterse 

[#4]

@dankuchma If you open an Excel file which was created in the pre-dynamic array times, Excel should automatically add those @ characters in every formula where it is needed.