Enable Autospill

%3CLINGO-SUB%20id%3D%22lingo-sub-2315800%22%20slang%3D%22en-US%22%3EEnable%20Autospill%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2315800%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20I%20need%20urgent%20help.%20I%20am%20trying%20to%20acquire%20a%20matrix%20by%20using%20a%20formula%20on%20a%20set%20of%20data%2C%20but%20instead%20of%20the%20formula%20spitting%20out%20a%20matrix%2C%20I%20just%20get%20the%20%23VALUE%20symbol%20in%20one%20cell.%20I%20don't%20know%20how%20to%20tell%20the%20program%20that%20the%20data%20need%20to%20spill%20into%20a%20matrix.%20Is%20there%20some%20setting%20I'm%20missing%3F%20Any%20help%20would%20be%20greatly%20appreciated.%20I%20am%20running%20the%20newest%20version%20of%20excel.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2315800%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2315972%22%20slang%3D%22en-US%22%3ERe%3A%20Enable%20Autospill%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2315972%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1043262%22%20target%3D%22_blank%22%3E%40matzindergard%3C%2FA%3E%26nbsp%3BRemove%20the%26nbsp%3B%40-signs%20in%20the%20formula%20and%20it%20might%20work%20as%20desired.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-05-03%20at%2005.50.06.png%22%20style%3D%22width%3A%20744px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F277422i47EFBDE42F15250C%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-05-03%20at%2005.50.06.png%22%20alt%3D%22Screenshot%202021-05-03%20at%2005.50.06.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2317262%22%20slang%3D%22en-US%22%3ERe%3A%20Enable%20Autospill%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2317262%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BThank%20you%20for%20responding%2C%20I'm%20not%20quite%20sure%20what%20you%20mean%20by%26nbsp%3B%40-signs%2C%20my%20formula%20looks%20exactly%20like%20your%20screenshot%20before%20and%20after%20I%20hit%20enter.%20It%20seems%20to%20work%20on%20my%20peers%20computers%2C%20but%20its%20frustrating%20because%20I%20can't%20get%20it%20to%20work%20on%20my%20person%20excel.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2317361%22%20slang%3D%22en-US%22%3ERe%3A%20Enable%20Autospill%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2317361%22%20slang%3D%22en-US%22%3EAre%20you%20certain%20that%20you%20have%20dynamic%20arrays%3F%20Office%20365%20has%20to%20be%20updated%20past%20a%20particular%20build%20version%20to%20have%20dynamic%20arrays%2C%20so%20you%20may%20need%20to%20update%20your%20software.%20Your%20formula%20works%20on%20my%20machine%20(office%202016)%2C%20but%20I%20have%20to%20enter%20it%20using%20the%20old%20method%20of%20selecting%20a%20range%20large%20enough%20for%20the%20array%20and%20then%20clicking%20in%20the%20formula%20bar%20and%20hitting%20Ctrl%2BShift%2BEnter.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2317367%22%20slang%3D%22en-US%22%3ERe%3A%20Enable%20Autospill%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2317367%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1043262%22%20target%3D%22_blank%22%3E%40matzindergard%3C%2FA%3E%26nbsp%3BWhen%20I%20open%20your%20file%20it%20looks%20like%20this%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-05-03%20at%2015.51.15.png%22%20style%3D%22width%3A%20610px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F277480i45FCD744DE6E56DA%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-05-03%20at%2015.51.15.png%22%20alt%3D%22Screenshot%202021-05-03%20at%2015.51.15.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EAs%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675152%22%20target%3D%22_blank%22%3E%40JMB17%3C%2FA%3E%26nbsp%3Bsuggests%2C%20your%20Excel%20version%20might%20not%20like%20the%20array%20formula%20and%20requires%20Ctrl-Shift-Enter.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello, I need urgent help. I am trying to acquire a matrix by using a formula on a set of data, but instead of the formula spitting out a matrix, I just get the #VALUE symbol in one cell. I don't know how to tell the program that the data need to spill into a matrix. Is there some setting I'm missing? Any help would be greatly appreciated. I am running the newest version of excel. 

6 Replies

@matzindergard Remove the @-signs in the formula and it might work as desired.

Screenshot 2021-05-03 at 05.50.06.png

@Riny_van_Eekelen Thank you for responding, I'm not quite sure what you mean by @-signs, my formula looks exactly like your screenshot before and after I hit enter. It seems to work on my peers computers, but its frustrating because I can't get it to work on my person excel. 

Are you certain that you have dynamic arrays? Office 365 has to be updated past a particular build version to have dynamic arrays, so you may need to update your software. Your formula works on my machine (office 2016), but I have to enter it using the old method of selecting a range large enough for the array and then clicking in the formula bar and hitting Ctrl+Shift+Enter.

@matzindergard When I open your file it looks like this:

Screenshot 2021-05-03 at 15.51.15.png

As @JMB17 suggests, your Excel version might not like the array formula and requires Ctrl-Shift-Enter.

 

Yes! It turns out I have to highlight the area and use ctl shift enter. Thank you both so much for your help this is a huge relief. I’m surprised my version doesn’t do it automatically but the fact that you were able to help me work around it makes it alright. Thank you both so much again you have no idea how helpful this is!