Excel 365 "If" function "Name" definition Spill error

%3CLINGO-SUB%20id%3D%22lingo-sub-3066319%22%20slang%3D%22en-US%22%3EExcel%20365%20%22If%22%20function%20%22Name%22%20definition%20Spill%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3066319%22%20slang%3D%22en-US%22%3E%3CP%3EExcel%202016%20and%20prior%20I%20would%20use%20the%20Name%20of%20headings%20and%20refer%20to%20this%20when%20going%20to%20the%20%22IF%22%20function%20to%20give%20a%20result%20to%20the%20if%20EQ%20question.%26nbsp%3B%20Using%20same%20process%20in%20365%26nbsp%3B%20get%20%22%23SPILL%22%20error.%20How%20do%20I%20get%20back%20to%20the%202016%20process%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3066319%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

Excel 2016 and prior I would use the Name of headings and refer to this when going to the "IF" function to give a result to the if EQ question.  Using same process in 365  get "#SPILL" error. How do I get back to the 2016 process?

 

 

5 Replies

@ChristopherGeorger Spilling is a great feature of the "new" dynamic array functionality in MS365 and E2021. But, if something gets in the way and the output can't be spilled you get that error. Same thing when you try to use such a formula inside a structured table. What exactly are you trying to do? What is the formula that is causing the error?

@Riny_van_Eekelen 

Yes,  Great.  I am using the "Name" function to indicate the top row above the work and have been able to run an "If" function elsewhere in the row and get the text in the top row (Name).  Able to do in Excel 2016 but not in Excel 365.

Thank you for your help if you can (disable #Spill or do not use top row as #Name?)

@ChristopherGeorger 

Better with sample. If we speak about structured table that will be [@[Name]] instead of [Name].

Sorry for typographical error....i am not a "Pro"
I use the @Name function to identify a range that has desired text and it is in the top row (AA1:AZ1) and call that range "Bike"
Below this row I use the @IF function to read a cell in row 2 and if "True" read the top row (1) and give me the "text" value in the "Bike" named range for that column. If argument is "False" load a "|" character so I can replace with " " later after pasting value. Spill# fills in the row as soon as I enter the first function and populated the whole row to the right (shaded)
It worked fine in Excel 2016 never saw #Spill until 365. How do I disable #Spill?

Thanks

@ChristopherGeorger 

Perhaps you may share if not file when couple of screenshots ? 

That how 365 works, but I don't know how to explain what to do without sample, we use different terminology.