PDA

View Full Version : Solved: Run out of screen



khalid79m
12-02-2008, 05:48 AM
With Range("Call_Checker")
.Formula = _
"=IF(COUNTBLANK(INDEX(Coach_Staff_Number,ROW(A1)))+COUNTBLANK(INDEX(Agent_Pi n,ROW(A1)))+COUNTBLANK(INDEX(Date,ROW(A1)))+COUNTBLANK(INDEX(Time,ROW(A1))) +COUNTBLANK(INDEX(TT,ROW(A1)))+COUNTBLANK(INDEX(Call_Type,ROW(A1)))+COUNTBL ANK(INDEX(CIN,ROW(A1)))+COUNTBLANK(INDEX(Monitored_By,ROW(A1)))+COUNTBLANK( INDEX(Monitors_Initials,ROW(A1)))+COUNTBLANK(INDEX(Monitoring_Method,ROW(A1 )))+COUNTBLANK(INDEX(Monitoring_Purpose,ROW(A1)))+COUNTBLANK(INDEX(Question _1,ROW(A1)))+COUNTBLANK(INDEX(Question_2,ROW(A1)))+COUNTBLANK(INDEX(Questio n_3,ROW(A1)))+COUNTBLANK(INDEX(Question_4,ROW(A1)))+COUNTBLANK(INDEX(Questi on_5,ROW(A1)))+COUNTBLANK(INDEX(Question_6,ROW(A1)))+COUNTBLANK(INDEX(Quest ion_7,ROW(A1)))+COUNTBLANK(INDEX(Question_8,ROW(A1)))+COUNTBLANK(INDEX(Ques tion_9,ROW(A1)))+COUNTBLANK(INDEX(Question_10,ROW(A1)))+COUNTBLANK(INDEX(Qu estion_11,ROW(A1)))+COUNTBLANK(INDEX(Question_12,ROW(A1)))+COUNTBLANK(INDEX (Question_13,ROW(A1)))+COUNTBLANK(INDEX(Question_14,ROW(A1))),""Data Missing"",""Call Complete"")"
'.Value = .Value
End With

this is my formula, I need to add in
+COUNTBLANK(INDEX(Question_15,ROW(A1)))

when I do this some of the formula jumps on to the next line adn messes up

khalid79m
12-02-2008, 05:53 AM
Ah the formula is too long ?

Can this be shrunk ?

Bob Phillips
12-02-2008, 06:17 AM
Break the formula down into parts in separate cells, then test the sum of those cells.

khalid79m
12-03-2008, 03:34 AM
for your advice worked a treat