Shift Rows Obstructing Spill

%3CLINGO-SUB%20id%3D%22lingo-sub-1972283%22%20slang%3D%22en-US%22%3EShift%20Rows%20Obstructing%20Spill%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1972283%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20getting%20a%20%22spill%20error%22%20when%20I%20try%20to%20reference%20a%20table%20from%20another%20worksheet%20because%20there's%20text%20in%20a%20cell%20that%20is%20in%20the%20way.%20I%20understand%20that.%20My%20question%20is%20whether%20there's%20a%20way%20to%20have%20the%20row%20of%20that%20obstructing%20cell%20automatically%20shift%20down%2C%20to%20allow%20all%20of%20the%20data%20from%20the%20table%20to%20come%20in.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EScott%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1972283%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1972345%22%20slang%3D%22en-US%22%3ERe%3A%20Shift%20Rows%20Obstructing%20Spill%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1972345%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F401085%22%20target%3D%22_blank%22%3E%40rosenbloomsnka%3C%2FA%3E%26nbsp%3BDon't%20think%20so!%20A%20dynamic%20array%20formula%20need%20space%20to%20spill%20its%20results%20into.%20As%20far%20as%20I%20know%20it%20can%20not%20automatically%20move%20occupied%20cells%20out%20of%20its%20way.%20You%20need%20to%20plan%20your%20work%20so%20that%20nothing%20is%20blocking%20the%20formula%20to%20output%20the%20results.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1972351%22%20slang%3D%22en-US%22%3ERe%3A%20Shift%20Rows%20Obstructing%20Spill%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1972351%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F401085%22%20target%3D%22_blank%22%3E%40rosenbloomsnka%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20my%20knowledge%20-%20no.%20That's%20usual%20formulas%20like%20INDEX()%20which%20return%20values%20one%20by%20one.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

I'm getting a "spill error" when I try to reference a table from another worksheet because there's text in a cell that is in the way. I understand that. My question is whether there's a way to have the row of that obstructing cell automatically shift down, to allow all of the data from the table to come in.

 

Thanks,

Scott

3 Replies

@rosenbloomsnka Don't think so! A dynamic array formula need space to spill its results into. As far as I know it can not automatically move occupied cells out of its way. You need to plan your work so that nothing is blocking the formula to output the results.

@rosenbloomsnka 

To my knowledge - no. That's usual formulas like INDEX() which return values one by one.

Mes fichiers excele ne s'ouvrent plus j'ai une message extension non valide ou fichier corrompu