#SPILL error

%3CLINGO-SUB%20id%3D%22lingo-sub-3220267%22%20slang%3D%22en-US%22%3E%23SPILL%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3220267%22%20slang%3D%22en-US%22%3ESo%20i%20am%20working%20on%20a%20dataset%20where%3CBR%20%2F%3EI%20am%20trying%20to%20match%20a%20set%20of%20dates%20if%20it%20falls%20in%20a%20certain%20date%20range%20interval%2C%20so%20whenever%20I%20apply%20a%20IF(D2%26gt%3B%24C%242%3A%24C%244%2C%22pass%22%2C%22fail%22)%20formula%20in%20D2%20(for%20example)%2C%20the%20rest%20of%20the%20cell%20automatically%20in%20column%20D%20gets%20filled%20with%20a%20blue%20dotted%20border%20around%20D%20column%20with%20the%20formula%2C%20but%20here%20is%20the%20thing%3A%3CBR%20%2F%3ESituation%201%3A%20The%20result%20in%20D3%20shows%20'pass'%20which%20is%20wrong.%20When%20i%20click%20once%20on%20D3%2C%20in%20the%20formula%20bar%2C%20the%20same%20formula%20in%20D2%20shows%20in%20grey%20colour%2C%20whereas%20since%20in%20ny%20formila%20D2%20is%20not%20locked%20it%20should%20change.%20Secondly%2C%20when%20i%20double%20click%20on%20D3%2C%20it%20shows%20that%20the%20cell%20is%20blank.%3CBR%20%2F%3ESituation%202%3A%20So%20leaving%20all%20that%2C%20I%20went%20to%20D2%20and%20dragged%20all%20the%20way%20down%20to%20manually%20fill%20the%20of%20column%20D%20with%20my%20formula%20then%20it%20shows%20%23spill%20error%2C%20when%20i%20select%20obstructing%20cells%20and%20delete%20that%2C%20itgoes%20back%20to%20situation%201%2C%20all%20showing%20'pass'%3CBR%20%2F%3E%3CBR%20%2F%3EWell%20i%20tried%20various%20formulas%2C%20multiple%20ways%20it%20still%20does%20the%20same.%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3220267%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-3220289%22%20slang%3D%22en-US%22%3ERe%3A%20%23SPILL%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3220289%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1319570%22%20target%3D%22_blank%22%3E%40Mahdia_Akter%3C%2FA%3E%3C%2FP%3E%3CP%3EIs%20the%20attached%20example%20what%20you%20are%20looking%20for%3F%20I%20entered%20the%20formula%20in%20E2%20and%20it%20returns%20the%20expected%20results.%20I%20can't%20enter%20the%20formula%20in%20D2%20because%20D2%20is%20included%20in%20the%20formula%2Fcalculation.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(D2%26gt%3B%24C%242%3A%24C%244%2C%22pass%22%2C%22fail%22)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Occasional Contributor
So i am working on a dataset where
I am trying to match a set of dates if it falls in a certain date range interval, so whenever I apply a IF(D2>$C$2:$C$4,"pass","fail") formula in D2 (for example), the rest of the cell automatically in column D gets filled with a blue dotted border around D column with the formula, but here is the thing:
Situation 1: The result in D3 shows 'pass' which is wrong. When i click once on D3, in the formula bar, the same formula in D2 shows in grey colour, whereas since in ny formila D2 is not locked it should change. Secondly, when i double click on D3, it shows that the cell is blank.
Situation 2: So leaving all that, I went to D2 and dragged all the way down to manually fill the of column D with my formula then it shows #spill error, when i select obstructing cells and delete that, itgoes back to situation 1, all showing 'pass'

Well i tried various formulas, multiple ways it still does the same.
1 Reply

@Mahdia_Akter

Is the attached example what you are looking for? I entered the formula in E2 and it returns the expected results. I can't enter the formula in D2 because D2 is included in the formula/calculation.

 

=IF(D2>$C$2:$C$4,"pass","fail")