dynamic arrays turn off

%3CLINGO-SUB%20id%3D%22lingo-sub-1593166%22%20slang%3D%22en-US%22%3Edynamic%20arrays%20turn%20off%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1593166%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20panicking%20because%20I%20really%20need%20to%20turn%20off%20the%20new%20dynamic%20array%20feature%20or%20I%20am%20........%20.%20I%20will%20illustrate%20this%20in%20an%20example%20and%20hope%20that%20there%20is%20a%20fix%20out%20there.%20Consider%20that%20I%20created%20an%20array%20in%20cells%20A1%3AA4%20in%20which%20the%20name%20of%20the%20array%20is%20given%20in%20cell%20A1%20as%20%22value%22.%20This%20would%20appear%20as%20given%20below%2C%20and%20I%20could%20then%20created%20the%20array%20by%20highlighting%20cells%20A1%3AA4%20and%20selecting%20%22Alt-I%2BN%2BC%22%20for%20Insert-Name-Create%20and%20selecting%20%22top-row%22%20for%20the%20location%20of%20the%20name.%26nbsp%3B%3C%2FP%3E%3CP%3EA1%20%3D%20value%3C%2FP%3E%3CP%3EA2%20%3D%205%3C%2FP%3E%3CP%3EA3%20%3D%208%3C%2FP%3E%3CP%3EA4%20%3D%2012%3C%2FP%3E%3CP%3EIt%20used%20to%20be%20that%20if%20I%20went%20to%20cell%20B3%20(or%20C3%2C%20D3%2C%20etc.)%20and%20entered%20%22%3Dvalue%22%20that%20it%20would%20return%20the%20value%20%228%22.%26nbsp%3B%20If%20I%20do%20this%20now%2C%20then%20it%20puts%20the%20values%20of%205%2C%208%2C%20and%2012%20in%20cells%20B3%20through%20B5.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20many%20thousands%20of%20hours%20of%20spreadsheet%20work%20invested%20in%20which%20I%20have%20relied%20on%20the%20use%20of%20arrays%20behaving%20like%20they%20used%20to.%20It%20is%20not%20uncommon%20that%20I%20have%20hundreds%20of%20defined%20arrays%2C%20each%20with%20several%20hundred%20entries%2C%20and%20then%20formulas%20with%20a%20dozen%20or%20more%20variables.%20These%20are%20used%20for%20making%20engineering%20design%20and%20analysis%20calculations.%20I%20did%20try%20saving%20the%20file%20as%20an%20older%20worksheet%2C%20but%20this%20did%20not%20help.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20that%20there%20is%20a%20fix%20out%20there.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1593166%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1593267%22%20slang%3D%22en-US%22%3ERe%3A%20dynamic%20arrays%20turn%20off%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1593267%22%20slang%3D%22en-US%22%3EInstead%20of%20entering%20%3Dvalue%2C%20enter%20%3D%40value.%3C%2FLINGO-BODY%3E
New Contributor

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 Replies
Instead of entering =value, enter =@value.

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