Forum Discussion
concatenate and if statements
Hi Steve,
I'm not sure with logic of your rules
"Same task last 4 weeks" - but you have only 4 weeks at all, what to compare?
"More than 4 tasks" - that's better use conditional formatting. Add rule to the cell D63
=(D63>4)
and apply it to entire names/weeks range (see attached)
"Sound vs usher" - if simply compare names when
=IF(OR(D$12=$D43,D$12=$D44,D$43=$D44),"conflict", "okay")
"Worship vs Ministry teams" - was in previous post
"Kids class vs Torah Portion" - again names?
=IF(D$35=$D28,"conflict", "okay")
"Worship vs Ushering" - compare team to name, but two times
=IF(
COUNTIF(OFFSET(WorshipTeam!$A$2,1,MATCH(D$21,WorshipTeam!$A$2:$E$2,0)-1,30),D$43) +
COUNTIF(OFFSET(WorshipTeam!$A$2,1,MATCH(D$21,WorshipTeam!$A$2:$E$2,0)-1,30),D$44), "conflict","okay")
"Teaching vs Ushering" - as above
=IF(OR(D$25=$D43,D$25=$D44,D$43=$D44),"conflict", "okay")
"More than 1 Liturgy" - I guess we shall compare if no matches in D17,D18 and D19,D20. When
=IF(SUMPRODUCT(--(D$17:D$18=D$19:D$20)),"conflict", "okay")
and in attached
Hi Sergei. I want to say how helpful you have been in my project. Really appreciate it. Working on SchHist now. Want to transcribe each month's entries into the appropriate rows without affecting the previous month's entries. Thought I could use an if function based on the date in LeadSch!L$2 then just add 30 days for subsequent months. Used this formula
=if(LeadSch!$L$2=1/6/2018,LeadSch!D$5)
should return Steve, actually returns FALSE. I assume I've got the syntax wrong, but I don't see it.
have modified formula to be
=if(LeadSch!$l$2=datevalue("1/6/2018")),LeadSch!D11)
but when I change the datevalue for subsequent months the value in earlier month shows FALSE and of course I want to preserve the earlier values.
Steve